Condition breakdown

[Mac1206]

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

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl64, width: 64"] Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mac1206]

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...

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

 

[stumac]

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.

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

 

[Mac1206]

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.

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

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

 

[xenou]

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.

 

[stumac]

the COND field changes repaetedly, this is a disaster load...i don't know if it can be accomplished.

Can you post a few more examples? It may still be possible with a bit of tweaking.

 

[Mac1206]

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

 

[Mac1206]

Can you post a few more examples? It may still be possible with a bit of tweaking.

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

 

[stumac]

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

 

[Mac1206]

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

This is the Desired result for this 1 line:
[TABLE="width: 1123"]
<tbody>[TR]
[TD]RULE_ID[/TD]
[TD]START_DATE[/TD]
[TD]END_DATE[/TD]
[TD]NAME[/TD]
[TD]COMPONENT[/TD]
[TD]COMPONENTVAL[/TD]
[TD]CURRENCY[/TD]
[TD]UOM[/TD]
[TD]COND[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD] 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[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 2188"]
<tbody>[TR]
[TD]RULE_ID[/TD]
[TD]START_DATE[/TD]
[TD]END_DATE[/TD]
[TD]NAME[/TD]
[TD]COMPONENT[/TD]
[TD]COMPONENTVAL[/TD]
[TD]CURRENCY[/TD]
[TD]UOM[/TD]
[TD]Sold to party[/TD]
[TD]City[/TD]
[TD]Pring Shape[/TD]
[TD]Size for Quality Certificate[/TD]
[TD]Plant[/TD]
[TD]Price Group[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]3/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]3/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]1/2"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]1/2"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]3/4"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]3/4"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]5/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]5/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]1"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SHAPE_RCS.HOTROLL.STRAIGHT[/TD]
[TD]1"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]3/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]3/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]1/2"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]1/2"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]3/4"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]3/4"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]5/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]5/8"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]1"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]24372[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD]100059180 ONEAL STEEL LITTLE ROCK SIGOSA/DIACERO S12,34,38,58,1 0401[/TD]
[TD]DISCOUNT[/TD]
[TD]2[/TD]
[TD]USD[/TD]
[TD]CWT[/TD]
[TD]100059180[/TD]
[TD]LITTLE ROCK[/TD]
[TD]109.BAR.SQUARE.HOTROLL.STRAIGHT[/TD]
[TD]1"[/TD]
[TD]RECOMMEND[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

 

[stumac]

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.