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

[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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
xenou said:
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!
Click to expand...

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
xenou said:
How did you end up with such a strange data set? Are you trying to reverse engineer something?
Click to expand...

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
xenou said:
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.
Click to expand...

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
xenou said:
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).
Click to expand...

About 125,000 and the condition is the same...Thanks in advance
 
Upvote 0
Mac1206 said:
About 125,000 and the condition is the same...Thanks in advance
Click to expand...
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
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Solved
DuckDuckGo Conditional Format
2
Replies
12
Views
816
MrDB4Excel
MrDB4Excel
J
  • Question Question
VBA: Conditional Formatting one column based on another
Replies
2
Views
457
jazzhayward
J
M
  • Question Question
Build HTML Table based on Loop through Excel Worksheet Column / Row condition
Replies
0
Views
946
mystik5
M
M
  • Question Question
Condition breakdown
Replies
0
Views
394
Mac1206
M
A
  • Question Question
VBA conditions clashed with one another
Replies
1
Views
700
alisoncleverly
A

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

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