comma separated data

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141
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
ABCD
1ModelOrderedoptionsColor1Trim
2CC10936AE7,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,1LS16U19D
3CC10936AE7,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,1LS25U83D
4CC10936AE7,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,1LS41U19D
51TD48FE9,L91,MM5,1VL11U81B
61TD48FE9,L91,MX0,1LS87U81B
7CC20743AE7,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,1WT50U88B
8CK20743AE7,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,1WT50U88B
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Code:
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
 
Upvote 0
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
ABCD
1ModelOrderedoptionsColor1Trim
2CC10936AG150U19D
3AG2
4AJ1
5AP3
6ASF
7AU3
8A95
9B30
10ETC
11ETC
12ETC
13CC10936AG141U83D
14AP2
15AN3
16G80
17ETC
18ETC
Sheet1
 
Upvote 0
My macro produces results like this:
Book2
ABCD
2CC10936AE716U19D
3CC10936AG116U19D
4CC10936AJ116U19D
5CC10936AP816U19D
6CC10936AU316U19D
7CC10936B3016U19D
8CC10936B5816U19D
9CC10936B8516U19D
10CC10936C4916U19D
11CC10936C5W16U19D
12CC10936DF516U19D
13CC10936DL816U19D
14CC10936EN416U19D
15CC10936FE916U19D
16CC10936GT416U19D
17CC10936JL416U19D
18CC10936K3416U19D
19CC10936LMG16U19D
20CC10936M3016U19D
21CC10936NZ416U19D
22CC10936N9316U19D
23CC10936QAN16U19D
24CC10936R9U16U19D
25CC10936SAF16U19D
26CC10936UE116U19D
27CC10936UK316U19D
28CC10936U2K16U19D
29CC10936VK316U19D
30CC10936V1K16U19D
Sheet2


Is that along the right lines?
 
Upvote 0
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.
 
Upvote 0
How do you mean cut off all the options except one? I'm not sure I follow.
 
Upvote 0

Forum statistics

Threads
1,218,840
Messages
6,144,790
Members
450,567
Latest member
Mplz

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