comma separated data

[jswaim]

Hi,
I would like to thank everyone who has tried to help me with a problem I am having. I have not been able to find the correct answer yet though. I am going to try a new angle on the problem.
I have four columns of data. Column A is for the Model, B is for options, C is for color, and D is for Trim. What I need to do is for each model I need to have the options in column B to be listed down the page without commas. Each 3 code option needs to have it's own cell. In the below example there are 8 models with their options, color, and trim. I cannot have the options mixed up with the other models. Hope this makes more sense.
Here is the raw data.

Book4
	A	B	C	D
1	Model	Orderedoptions	Color1	Trim
2	CC10936	AE7,AG1,AJ1,AP8,AU3,B30,B58,B85,C49,C5W,DF5,DL8,EN4,FE9,GT4,JL4,K34,LMG,M30,NZ4,N93,QAN,R9U,SAF,UE1,UK3,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,1LS	16U	19D
3	CC10936	AE7,AG1,AJ1,AP8,AU3,B30,B58,B85,C49,C5W,DF5,DL8,EN4,FE9,GT4,JL4,K34,LMG,M30,NZ4,N93,QAN,R9U,SAF,UE1,UK3,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,1LS	25U	83D
4	CC10936	AE7,AG1,AJ1,AP8,AU3,B30,B58,B85,C49,C5W,DF5,DL8,EN4,FE9,GT4,JL4,K34,LMG,M30,NZ4,N93,QAN,R9U,SAF,UE1,UK3,U2K,VK3,V1K,V54,ZRS,ZW9,ZY1,1LS	41U	19D
5	1TD48	FE9,L91,MM5,1VL	11U	81B
6	1TD48	FE9,L91,MX0,1LS	87U	81B
7	CC20743	AE7,AJ1,AU3,BG9,C6W,C67,DE2,FE9,GT4,KNP,K34,LY6,MYD,NZ3,PCX,QB5,QIZ,R9U,SAF,UE1,U1C,VK3,V22,V76,ZIZ,ZY1,Z82,Z85,1WT	50U	88B
8	CK20743	AE7,AU3,B30,C6W,C67,DE2,FE9,GT4,G80,KNP,K34,LY6,MYD,NZZ,NZ3,PCX,QB5,QIZ,SAF,UE1,U1C,U2J,VK3,V22,V76,ZIZ,ZY1,Z82,Z85,1WT	50U	88B
Sheet1

 

[jswaim]

Did you happen to see the example that I sent of how the data should look when it is finished? I need it this way, because I have a great macro that will crunch this data. The data just needs to be put in that format so it will read it.

 

[Datsmart]

I adjusted Lewiy's fine code. See if this is what you want?

Sub MyMacro()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim Options As String
Dim MaxOption As Long
Dim Limit As Long, Limit2 As Long
Dim c As Long, d As Long
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Limit2 = 2
For c = 2 To Limit
    With WorksheetFunction
        MaxOption = Len(sh1.Cells(c, 2)) - Len(.Substitute(sh1.Cells(c, 2), ",", "")) + 1
    End With
        sh2.Cells(Limit2, 1) = sh1.Cells(c, 1)
        sh2.Cells(Limit2, 3) = sh1.Cells(c, 3)
        sh2.Cells(Limit2, 4) = sh1.Cells(c, 4)
    For d = 1 To MaxOption
        Options = Mid(Cells(c, 2), d + ((d - 1) * 3), 3)
        sh2.Cells(Limit2, 2) = Options
        Limit2 = Limit2 + 1
    Next d
Next c
End Sub

Sheet 2 will have the results.

 

[Datsmart]

By the way. You have three duplicate posts that you should Delete from this forum:
http://www.mrexcel.com/board2/viewtopic.php?p=1388206
http://www.mrexcel.com/board2/viewtopic.php?p=1388215
http://www.mrexcel.com/board2/viewtopic.php?p=1388217

Click the "X" in the upper corner of your extra posts and answer "Yes".
Only you or the forum moderator can delete them.

 

[MorganO]

Jason,

Since the post I was working with you on was locked (I didn't realize you had THREE seperate posts working the same issue......) I am posting my reply here. If you use the same code I posted before to sort the data, you can achieve your need to:

What I am trying to do is find out for instance how many CC10936's with the 1LT package I have with the option U2K. Or how many of my CC10936's I have that have that have the 1LT package and have the color code 16U.

With some easy worksheet functions. Using VBA to achieve this is not the right solution in my book.

Here is an example worksheet using a sample of your data to show you how to do this:

Book3.xls
	A	B	C	D	E	F	G	H
1	Model	Orderedoptions	Color1	Trim	Model	Option	Color	Trim
2	CC10936	1LS	16U	19D	CC10936	1LS	16U	19D
3	CC10936	ZY1	16U	19D	MatchModel,OptionandTrim	1
4	CC10936	ZW9	16U	19D	MatchModel,Option,andColor	2
5	CC10936	ZRS	16U	19D	MatchModelandOption	2
6	CC10936	V54	16U	19D	MatchModel	13
7	CC10936	1LS	16U	19F	MatchOption	2
8	CC10936	VK3	16U	19D
9	CC10936	U2K	16U	19E
10	CC10936	UK3	16U	19F
11	CC10936	UE1	16U	19D
12	CC10936	SAF	16U	19D
13	CC10936	R9U	16U	19D
14	CC10936	QAN	16U	19D
Sheet1

You see that I first ran my code to separate your comma delimited data into distinct rows of data. I am then using Cells E thru H to answer your questions. You put the data you are looking for in Cells E2, F2, G2, H2. Then Cells F3 thru F7 use SUMPRODUCT functions to extract the answers from the data. You can build on these answers easily by using different versions of the SUMPRODUCT function.

Also, please avoid duplicate posting in the future.

Take care.

Owen

 

[jswaim]

Thanks You all very much for your help. The problem here is solved.
ExcelingInTheAirForce I have not tried yours again yet. I will as soon as I get some more time. I will let you know if it will work as well. Thanks again.
Take Care,
Jason