Condition breakdown

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

[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]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This is very weird. Can you explain more? In database design generally you do not import conditions as columns in tables - you use conditions to select from columns in tables!
 
Upvote 0
This is very weird. Can you explain more? In database design generally you do not import conditions as columns in tables - you use conditions to select from columns in tables!

You are correct, I'm trying to import an excel file that has a Condition column which need to be converted into fields and rows...My row J2 is this one line : Sold to party = 100092131 And Price Group = 27 And Shipping condition = RR And Price Group Includes 19,22,25,27,29

In order for me to import into access, it need to be converted somehow into this format as column and rows below:


[TABLE="width: 577"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] SOLD TO PARTY [/TD]
[TD] PRICE GROUP [/TD]
[TD] SHIPPING CONDITION [/TD]
[TD] PRICE GROUP INCLUDES [/TD]
[/TR]
[TR]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]29[/TD]
[/TR]
</tbody>[/TABLE]


May this is a VBA or Excel formulas functionality that could convert this into the right format for importing instead of a manual process for several thousand records...
 
Upvote 0
How did you end up with such a strange data set? Are you trying to reverse engineer something?
 
Upvote 0
How did you end up with such a strange data set? Are you trying to reverse engineer something?

It came as an Excel file, which is weird indeed as such (Sample Data):

[TABLE="width: 1152"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><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="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD] 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]
The desired result is:

[TABLE="width: 1431"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><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] PRICE GROUP [/TD]
[TD] SHIPPING CONDITION [/TD]
[TD] PRICE GROUP INCLUDES [/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]8/27/2008[/TD]
[TD][/TD]
[TD]CW Carry Rail Direct Discount W,N[/TD]
[TD]DISCOUNT[/TD]
[TD].5[/TD]
[TD]CAD[/TD]
[TD]CWT[/TD]
[TD="align: right"]100092131[/TD]
[TD="align: right"]27[/TD]
[TD] RR [/TD]
[TD="align: right"]29
[/TD]
[/TR]
</tbody>[/TABLE]
Is there anyway to convert that last column so it would pull out the fields and what it equals?
 
Upvote 0
Okay but why would you assume this:
Code:
100092131 	27 	RR 	19
100092131 	27 	RR 	22
100092131 	27 	RR 	25
100092131 	27 	RR 	27
100092131 	27 	RR 	29
instead of this:
Code:
100092131 	27 	RR 	27

which is to say, just because 27 is included in 19,22,25,27 and 29 doesn't mean 27 is 19,22,25,27 and 29 ... it could be just itself (27).

what you are asking for requires some fairly heavy duty data massaging. There is no button to press for that. It remains quite mysterious how you are interpreting this COND column and why the data is coming to you that way.
 
Last edited:
Upvote 0
Okay but why would you assume this:
Code:
100092131     27     RR     19
100092131     27     RR     22
100092131     27     RR     25
100092131     27     RR     27
100092131     27     RR     29
instead of this:
Code:
100092131     27     RR     27

which is to say, just because 27 is included in 19,22,25,27 and 29 doesn't mean 27 is 19,22,25,27 and 29 ... it could be just itself (27).

what you are asking for requires some fairly heavy duty data massaging. There is no button to press for that. It remains quite mysterious how you are interpreting this COND column and why the data is coming to you that way.

I understand but that's my directive to see if it can be done with this excel file and you are correct with 100092131 27 RR 27...
 
Upvote 0
okay. Do you have any examples of more COND values or do they all follow exactly this kind of structure? For instance, they can probably be split out on the AND values and the = symbols, but that means they must all have the same conditions applied. If they don't, it's even uglier (such as rows that don't have the includes condition or rows that don't have the shipping condition condition).

Approx. how much data is there (not a big deal but 1,000,000 rows would be a lot different than 1,000 rows in terms of building a solution).
 
Last edited:
Upvote 0
okay. Do you have any examples of more COND values or do they all follow exactly this kind of structure? For instance, they can probably be split out on the AND values and the = symbols, but that means they must all have the same conditions applied. If they don't, it's even uglier (such as rows that don't have the includes condition or rows that don't have the shipping condition condition).

Approx. how much data is there (not a big deal but 1,000,000 rows would be a lot different than 1,000 rows in terms of building a solution).

About 125,000 and the condition is the same...Thanks in advance
 
Upvote 0
About 125,000 and the condition is the same...Thanks in advance
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...
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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