separating and crunching data

[jswaim]

Hello Again,
I think I would like to try to explain what I am trying to do one more time. Hope it makes sense. I have four colums labled Model, Ordered options, Color 1, and Trim.
The model in column A for example is CK10936, the ordered options are in B (all separated by a comma) The Color is shown below as 59U is in Column C and the Trim is 193. Columns A - D are all that are used.
Here is what I am trying to accomplish.
I would like to know how many of each model I have with different options. The very last option in each cell is in column B, this is the package level for the model. For example, I would like to know how many CK10936's I have with 3LT, 2LT, 1LT package level. I would also like to know for example, how many CK10936's with the 3LT package level I have with the Option CF5 in located in Column B. I would like to repeat this for each model until all the data is crunched and separated by the package level. Under each model and package level I would like to see the respective options from greatest to least. Thanks in advance for any help at all. Data is below.

Model	Ordered options	Color 1	Trim
CK10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,BVV,B30,B58,B71,B85,CE1,CF5,CJ2,C49,C5Z,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,K47,LC9,M30,NZZ,N87,QXN,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVA,U2K,U42,VK3,V1K,V54,V76,XA7,ZRS,ZW9,ZY1,Z71,3LT	59U	193
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CE1,CF5,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G69,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVB,UVC,U2K,U42,VGK,VK3,V1K,V54,XA7,ZRS,ZW9,ZY1,Z55,1LZ	41U	313
CK10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CE1,CJ2,C49,C5Z,DF5,DL3,EN4,FE9,GT4,G69,G80,JF4,JL4,K34,LC9,M30,NZ4,QSS,RCS,SAF,T96,UD7,UE1,UG1,UK3,UQA,US9,U2K,VGK,VK3,V1K,V54,V76,XA7,ZRS,ZW9,ZY1,Z55,1LZ	41U	193
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CF5,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,SAF,T96,UD7,UE1,UG1,UK3,UQA,US9,U2K,VK3,V54,ZRS,ZW9,ZY1,3LT	50U	193
CK10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,BVV,B30,B58,B71,B85,CJ2,C49,C5Z,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,K47,LC9,M30,NZZ,NZ4,N87,QXN,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,US9,U2K,VK3,V1K,V54,V76,ZRS,ZW9,ZY1,Z71,3LT	59U	193
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CE1,CF5,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G69,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVA,U2K,U42,VGK,VK3,V1K,V22,V54,XA7,ZRS,ZW9,ZY1,Z55,1LZ	50U	313
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,US9,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,3LT	50U	193
CC10936	AG1,AJ1,AP8,AU3,A95,B30,B58,B85,C49,C5W,DF5,DL8,EN4,FE9,GT4,G80,JF4,JL4,K34,LMG,M30,NZ4,P46,QAN,R9U,SAF,T96,UD7,UE1,UK3,UQA,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,1LT	41U	19D
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CE1,CF5,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G69,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVA,U2K,U42,VGK,VK3,V1K,V22,V54,XA7,ZRS,ZW9,ZY1,Z55,1LZ	74U	193
CC10936	AG1,AJ1,AP8,AU3,A95,B30,B58,B85,C49,C5W,DF5,DL8,EN4,FE9,GT4,G80,JF4,JL4,K34,LMG,M30,NZ4,P46,QAN,R9U,SAF,T96,UD7,UE1,UK3,UQA,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,1LT	41U	19D
CC10936	AG1,AG2,AJ1,AN3,AP3,ASF,AU3,B30,B58,B85,CE1,CF5,CJ2,C49,C5W,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,LMG,M30,NZ4,QSS,RCS,R9U,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVA,U2K,U42,VK3,V1K,V54,XA7,ZRS,ZW9,ZY1,3LT	41U	313

 

[jswaim]

if anyone knows how to at least convert the data in column B from comma separated text to each option being listed into a single cell going down column B. The only stipulation is that each model code in Column A must correspond to the options and Color Code in Column B-D. In other words, I cannot get the options mixed up with the model codes. Each row for Columns A-D is one unique unit. Please help. Thanks.
Jason

 

[Norie]

I've looked at this post and your other one but it's still unclear what you want and what the data represents.

You need to remember this is 'your' data.

Perhaps if you posted using Colo's HTML Maker it would be clearer, you'll find a link for that in my sig.

 

[jswaim]

I am not sure how to use the HTML maker. I tried downloading the the file, but my companies firewall will not let it happen.

First, I would like to thank you for taking a look at my problem.

Here is what I have: I am using columns A-D. column A has model codes, Column B has all the option codes separated by a comma that pertains to the model code in column A, Column C has the color of the model, and Column D has the Trim level.

The last option in Column B is the package (1LT or 2LT or 3LT etc.) I am trying to separate data and determine how many CC10936's I have that are 1LT, 2LT, 3LT, etc.
I would like it to look like this at the top of the page. The ='s represents the number of CC10936's that are 1LT and have the option AE7.
CC10936 CC10936
1LT=5 2LT=3
AE7=2 AE7=1
B30=5 B30=2
Something like that.
Next, I would like to have the data that meets the requirments of the model code (CC10936) and the equipment group (1LT, 2LT, etc.) to then be process for other options. Basically, I want to know how many CC10936's I have that are the 1LT package and have options AE7, B30, G80, etc.
I know this is pretty confusing, sorry. I am doing my best to make this easy.
thanks,

 

[Norie]

Can you clarify what's in each column?

The model code is clear but the rest isn't.

For example is this correct?

Column A = CK10936

Column B = AG1,AG2,AJ1,AN3,AP3,ASF,AU3,BVV,B30,B58,B71,B85,CE1,CF5,CJ2,C49,C5Z,DF5,DL3,EN4,FE9,GT4,G80,JF4,JL4,K34,K47,LC9,M30,NZZ,N87,QXN,SAF,T96,UD7,UE1,UG1,UK3,UQA,UVA,U2K,U42,VK3,V1K,V54,V76,XA7,ZRS,ZW9,ZY1,Z71,3LT

Column C = 59U

Column D = 193

 

[jswaim]

That is correct. Notice all the options in column B. The very last code is the package group.

 

[Joe4]

Duplicate. See re-stated question here:
http://www.mrexcel.com/board2/viewtopic.php?t=288254&highlight=