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

 

[dave3009]

I would probably use Data>>Validation>>List for that

Perhaps creating a new column go down it cell by cell copy the long text (the text not the cell) and paste it into Source part of Validation List

HTH


Dave

 

[piyushdabomb]

response

Hi,

I am not quite sure I understand your request/problem. Please provide an example.

It looks like in the second column you have more than the stated number of 'models'.

Let us know..an example is a lot more useful to understand.

-> If possible, please provide an explanation of the use to the business problem so that if we can't find a direct solution, we can provide alternative solutions.

 

[piyushdabomb]

response

Hi,

I am not quite sure I understand your request/problem. Please provide an example.

It looks like in the second column you have more than the stated number of 'models'.

Let us know..an example is a lot more useful to understand.

-> If possible, please provide an explanation of the use to the business problem so that if we can't find a direct solution, we can provide alternative solutions.

 

[jswaim]

Column A is where the models are. Column B are the models option codes. Column C is where the color codes are and Column D is where the Trim codes exist.
So from A2 to D2 is one unit unique unit.
Does this help?

 

[Lewiy]

Does this macro do what you need?
It assumes your worksheet is called Sheet1 and you have a spare sheet called Sheet2 where the result is sent:

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
    For d = 1 To MaxOption
        sh2.Cells(Limit2, 1) = sh1.Cells(c, 1)
        sh2.Cells(Limit2, 3) = sh1.Cells(c, 3)
        sh2.Cells(Limit2, 4) = sh1.Cells(c, 4)
        Options = Mid(Cells(c, 2), d + ((d - 1) * 3), 3)
        sh2.Cells(Limit2, 2) = Options
        Limit2 = Limit2 + 1
    Next d
Next c
End Sub

 

[jswaim]

This formula is leaving out all the options in column B except one. I need it to list all the options as they are in Column B except I need the options to have their own cell without commas.

Here is an example of how I need the end result.

Book7
	A	B	C	D
1	Model	Orderedoptions	Color1	Trim
2	CC10936	AG1	50U	19D
3		AG2
4		AJ1
5		AP3
6		ASF
7		AU3
8		A95
9		B30
10		ETC
11		ETC
12		ETC
13	CC10936	AG1	41U	83D
14		AP2
15		AN3
16		G80
17		ETC
18		ETC
Sheet1

 

[Lewiy]

My macro produces results like this:

Book2
	A	B	C	D
2	CC10936	AE7	16U	19D
3	CC10936	AG1	16U	19D
4	CC10936	AJ1	16U	19D
5	CC10936	AP8	16U	19D
6	CC10936	AU3	16U	19D
7	CC10936	B30	16U	19D
8	CC10936	B58	16U	19D
9	CC10936	B85	16U	19D
10	CC10936	C49	16U	19D
11	CC10936	C5W	16U	19D
12	CC10936	DF5	16U	19D
13	CC10936	DL8	16U	19D
14	CC10936	EN4	16U	19D
15	CC10936	FE9	16U	19D
16	CC10936	GT4	16U	19D
17	CC10936	JL4	16U	19D
18	CC10936	K34	16U	19D
19	CC10936	LMG	16U	19D
20	CC10936	M30	16U	19D
21	CC10936	NZ4	16U	19D
22	CC10936	N93	16U	19D
23	CC10936	QAN	16U	19D
24	CC10936	R9U	16U	19D
25	CC10936	SAF	16U	19D
26	CC10936	UE1	16U	19D
27	CC10936	UK3	16U	19D
28	CC10936	U2K	16U	19D
29	CC10936	VK3	16U	19D
30	CC10936	V1K	16U	19D
Sheet2

Is that along the right lines?

 

[jswaim]

I see that, but it does not produce the results I need. The last reply I sent is how the data needs to look in it's finished form. Your macro seems to cut off all the options in Column B except one. If I knew how to tweak your code I would, but I do not know how.

 

[Lewiy]

How do you mean cut off all the options except one? I'm not sure I follow.