Condition breakdown

M

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
I have this COND column in Excel which I need to import into Access but as you see, each row has this long string that needs to be converted into rows: Sold to party, Price Group and Shipping Condition would need to be represented 5 times for this particular Price Group....I'm at a lost trying to convert this....Thanks in Advance

Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29

<tbody>
</tbody>
 
Sort by date Sort by votes
Mac1206 said:
Good morning Xenou, I had to verify that the code layout above and for each price within the group, it is assigned as follows:

Desired Result Code:
100092131 27 RR 19
100092131 27 RR 22
100092131 27 RR 25
100092131 27 RR 27
100092131 27 RR 29

I hope this doesn't make it more difficult but I know this type of programming is difficult anyway....I'm learning that...
Click to expand...

The Count is approximately 25k rows for my part, not as bad as I initially thought except the process...
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
If your condition field is always the same format, then you can use the split function to breakout each condition and then again to breakout each price in the price group includes. link a copy of your spread sheet to an access database and then create a table with the fields you require to be included (these are shown in Green in the code below).

Insert a new module and paste the code below in and run it... the field names in both the linked spread sheet and the new table must match, if not you will get an error at the line it doesn't match.

Code: 
Sub SplitConditions()
Set rs1 = CurrentDb.OpenRecordset("Conditions") '[B][COLOR=#ff0000]Change this to be the name of your linked table[/COLOR][/B]
Set rs2 = CurrentDb.OpenRecordset("TblCondBreakdown") [B][COLOR=#ff0000]'Change this to be the name of your Newly created table[/COLOR][/B]
If Not (rs1.EOF And rs1.BOF) Then
    rs1.MoveFirst
      Do Until rs1.EOF = True
      
      Cond = Split(rs1!Cond, " And ")
      
      Price = Split(Right(Cond(3), Len(Cond(3)) - 21), ",")
      
      For x = 0 To UBound(Price)
        rs2.AddNew
        rs2[COLOR=#008000]!Rule_ID = [/COLOR]rs1!Rule_ID
        [COLOR=#000000]rs2![/COLOR][COLOR=#008000]start_date [/COLOR]= rs1!start_date
        rs2![COLOR=#006400]End_date [/COLOR]= rs1!End_date
        rs2![COLOR=#006400]CompName[/COLOR] = rs1![Name]
        rs2[COLOR=#006400]!component [/COLOR]= rs1!component
        rs2![COLOR=#006400]componentval[/COLOR] = rs1!componentval
        rs2![COLOR=#006400]Currency[/COLOR] = rs1!Currency
        rs2![COLOR=#006400]Uom [/COLOR]= rs1!Uom
    [COLOR=#000000]    rs2[/COLOR][COLOR=#006400]!Sold_To_Party = [/COLOR]Right(Cond(0), Len(Cond(0)) - 16)
        rs2[COLOR=#006400]!Price_Group = [/COLOR]Right(Cond(1), Len(Cond(1)) - 14)
        rs2![COLOR=#006400]Shipping_Condition [/COLOR]= Right(Cond(2), Len(Cond(2)) - 21)
        rs2![COLOR=#006400]Price_Group_Includes = [/COLOR]Price(x)
        rs2.Update
            
       Next x
         rs1.MoveNext
    Loop
End If
Set rs1 = Nothing
Set rs2 = Nothing
MsgBox "Split complete"
End Sub
 
Last edited:
Upvote 0
stumac said:
If your condition field is always the same format, then you can use the split function to breakout each condition and then again to breakout each price in the price group includes. link a copy of your spread sheet to an access database and then create a table with the fields you require to be included (these are shown in Green in the code below).

Insert a new module and paste the code below in and run it... the field names in both the linked spread sheet and the new table must match, if not you will get an error at the line it doesn't match.

Code: 
Sub SplitConditions()
Set rs1 = CurrentDb.OpenRecordset("Conditions") '[B][COLOR=#ff0000]Change this to be the name of your linked table[/COLOR][/B]
Set rs2 = CurrentDb.OpenRecordset("TblCondBreakdown") [B][COLOR=#ff0000]'Change this to be the name of your Newly created table[/COLOR][/B]
If Not (rs1.EOF And rs1.BOF) Then
    rs1.MoveFirst
      Do Until rs1.EOF = True
      
      Cond = Split(rs1!Cond, " And ")
      
      Price = Split(Right(Cond(3), Len(Cond(3)) - 21), ",")
      
      For x = 0 To UBound(Price)
        rs2.AddNew
        rs2[COLOR=#008000]!Rule_ID = [/COLOR]rs1!Rule_ID
        [COLOR=#000000]rs2![/COLOR][COLOR=#008000]start_date [/COLOR]= rs1!start_date
        rs2![COLOR=#006400]End_date [/COLOR]= rs1!End_date
        rs2![COLOR=#006400]CompName[/COLOR] = rs1![Name]
        rs2[COLOR=#006400]!component [/COLOR]= rs1!component
        rs2![COLOR=#006400]componentval[/COLOR] = rs1!componentval
        rs2![COLOR=#006400]Currency[/COLOR] = rs1!Currency
        rs2![COLOR=#006400]Uom [/COLOR]= rs1!Uom
    [COLOR=#000000]    rs2[/COLOR][COLOR=#006400]!Sold_To_Party = [/COLOR]Right(Cond(0), Len(Cond(0)) - 16)
        rs2[COLOR=#006400]!Price_Group = [/COLOR]Right(Cond(1), Len(Cond(1)) - 14)
        rs2![COLOR=#006400]Shipping_Condition [/COLOR]= Right(Cond(2), Len(Cond(2)) - 21)
        rs2![COLOR=#006400]Price_Group_Includes = [/COLOR]Price(x)
        rs2.Update
            
       Next x
         rs1.MoveNext
    Loop
End If
Set rs1 = Nothing
Set rs2 = Nothing
MsgBox "Split complete"
End Sub
Click to expand...

Thanks but in viewing the data they want to upload, the COND field changes repaetedly, this is a disaster load...i don't know if it can be accomplished...omg
 
Upvote 0
I was thinking along exactly the same lines of stumac - using a recordset along with the data in Access and splitting out the COND field.
 
Upvote 0
the COND field changes repaetedly, this is a disaster load...i don't know if it can be accomplished.
Click to expand...

Can you post a few more examples? It may still be possible with a bit of tweaking.
 
Upvote 0
Good morning xenou, after looking the COND field for the upload, the conditions changes repeatedly...See the 3 COND rows below for an example...omg

Sold to party = 100092157 And Shipping Plant Includes 1301,1302 And Shipping condition = RR And Price Group Includes 19,22,25,27,29
Sold to party = 100091804 And Plant = RECOMMEND And Pricing Shape = 106.HPILE.SHAPE_H.HOTROLL.STRAIGHT And Deal Code = D4000 And Shipping condition Includes PU,RR
Division = 02 And Deal Code = NULL And Sold to party Includes 100091838,100091864,100091848,100064397,100091691,100093001 And Order Created On <= 04/01/2016 And Plant = RECOMMEND And Price Group Includes 20,23

This seems impossible to me...omg
 
Last edited:
Upvote 0
stumac said:
Can you post a few more examples? It may still be possible with a bit of tweaking.
Click to expand...

Here is a few more examples for you...Thanks so much for helping me on this regardless if we can or can't...Thanks

Sold to party = 100092157 And Shipping Plant Includes 1301,1302 And Shipping condition = RR And Price Group Includes 19,22,25,27,29
Sold to party = 100091804 And Plant = RECOMMEND And Pricing Shape = 106.HPILE.SHAPE_H.HOTROLL.STRAIGHT And Deal Code = D4000 And Shipping condition Includes PU,RR
Division = 02 And Deal Code = NULL And Sold to party Includes 100091838,100091864,100091848,100064397,100091691,100093001 And Order Created On <= 04/01/2016 And Plant = RECOMMEND And Price Group Includes 20,23
Price Group = 25 And City = AMOS And Shipping condition = PU And Shipping Plant Includes 1302,1301 And Price Group Includes 19,22,25,27,29
Same Shape Size Load Quantity = 1 And Region = QCCA And Shipping Plant Includes 1301,1302 And Sold to party = 100060551 And Price Group Includes 19,22,25,27,29
Sold to party Excludes 100092174,100092168,100092473,100092474,100092475,100060551,100060599,100092204,100061219,100061247,100061285,100061278,100061270,100092558 And Product = ANGLE And Size for Quality Certificate Includes 3 1/2X3 1/2X1/4,5X3 1/2X5/16,5X3 1/2X1/4,5X3 1/2X3/8,6X3 1/2X5/16,6X4X3/8,6X4X5/16,7X4X3/8 And Steel Grade Contains W And Deal Code = MERC1 And Price Group Includes 19,22,25,27,29
Sold to party = 100108837 And Order = 317609
Sold to party Includes 100091368 And Plant = RECOMMEND And Pricing Shape = 106.SHEET_PILE.SHAPE_PZC.HOTROLL.STRAIGHT And Deal Code = Z5400 And Shipping condition Includes PU,RR
Sold to party = 100093037 And City = POMONA And Plant = 1338 And Region = CAUS And Pricing Shape Includes 110.REBAR.ROUND.HOTROLL.STRAIGHT,110.REBAR.ROUND.HOTROLL_T.STRAIGHT And DECQ >= 001.750-000.000-000.000 And DECQ <= 002.250-000.000-000.000 And Steel Grade Includes A706-60,60/A706-60,A706 60/420,42 A706 And Price Group Includes 20,23
Sold to party <> 100093107 And Pricing Shape = 205.BAR.ROUND.HOTROLL.STRAIGHT And Size for Quality Certificate Includes 63M LH And Steel Grade Contains 80 And Plant = RECOMMEND
 
Upvote 0
Would you be expecting all of this to be split and saved - e.g:

What would you expect this to look like after splitting it:

Sold to party Excludes 100092174,100092168,100092473,100092474,100092475,100060551,100060599,100092204,100061219,100061247,100061285,100061278, 100061270,100092558 And Product = ANGLE And Size for Quality Certificate Includes 3 1/2X3 1/2X1/4,5X3 1/2X5/16,5X3 1/2X1/4,5X3 1/2X3/8,6X3 1/2X5/16,6X4X3/8,6X4X5/16,7X4X3/8 And Steel Grade Contains W And Deal Code = MERC1 And Price Group Includes 19,22,25,27,29
 
Upvote 0
stumac said:
Would you be expecting all of this to be split and saved - e.g:

What would you expect this to look like after splitting it:

Sold to party Excludes 100092174,100092168,100092473,100092474,100092475,100060551,100060599,100092204,100061219,100061247,100061285,100061278, 100061270,100092558 And Product = ANGLE And Size for Quality Certificate Includes 3 1/2X3 1/2X1/4,5X3 1/2X5/16,5X3 1/2X1/4,5X3 1/2X3/8,6X3 1/2X5/16,6X4X3/8,6X4X5/16,7X4X3/8 And Steel Grade Contains W And Deal Code = MERC1 And Price Group Includes 19,22,25,27,29
Click to expand...

This is the Desired result for this 1 line:
RULE_IDSTART_DATEEND_DATENAMECOMPONENTCOMPONENTVALCURRENCYUOMCOND
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT Sold to party = 100059180 And City = LITTLE ROCK and Pricing Shape Includes 109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT,109.BAR.SQUARE.HOTROLL.STRAIGHT And Size for Quality Certificate Includes 3/8",1/2",3/4",5/8",1" And Plant = RECOMMEND And Price Group Includes 20,23

<tbody>
</tbody>
RULE_IDSTART_DATEEND_DATENAMECOMPONENTCOMPONENTVALCURRENCYUOMSold to partyCityPring ShapeSize for Quality CertificatePlantPrice Group
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT3/8"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT3/8"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT1/2"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT1/2"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT3/4"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT3/4"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT5/8"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT5/8"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT1"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT1"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT3/8"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT3/8"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT1/2"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT1/2"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT3/4"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT3/4"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT5/8"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT5/8"RECOMMEND23
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT1"RECOMMEND20
243724/1/201712/31/2017100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401DISCOUNT2USDCWT100059180LITTLE ROCK109.BAR.SQUARE.HOTROLL.STRAIGHT1"RECOMMEND23

<tbody>
</tbody>
 
Last edited:
Upvote 0
Ahhh, ok - it looks pretty complicated then. It would take lot of IF statements along with loops in vba to code that up - and given that we have only seen a few examples from a dataset of 125k this is likely to continue to expand a fair bit.

You may be able to use the code I provided above and step through the process several times splitting out a condition at a time and then amending the code for the next field, however, you would need a definite list of possible fields that would come up to ensure you don't lose any data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Solved
DuckDuckGo Conditional Format
2
Replies
12
Views
389
MrDB4Excel
MrDB4Excel
M
  • Question
Build HTML Table based on Loop through Excel Worksheet Column / Row condition
Replies
0
Views
784
mystik5
M
M
  • Question
Condition breakdown
Replies
0
Views
342
Mac1206
M
A
  • Question
VBA conditions clashed with one another
Replies
1
Views
638
alisoncleverly
A
FrumpyJones
  • Question
Conditional Formatting: Can I base it off of a group of other CF's?
Replies
3
Views
621
Fluff
Fluff

Forum statistics

Threads
1,216,014
Messages
6,128,282
Members
449,436
Latest member
blaineSpartan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top