You could try a macro. Check here for some directions if you are unfamiliar
w/macros. Pay particular attention to "Using someone else's macro." Thanks
to Dave for making these instructions available - can't say how often it has
come in handy.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
rngData is where your data is currently. It assumes your data is in
*adjacent* columns. rngDest is where you want the data to get copied to.
Change the worksheet and range references for these two variables as needed.
BE SURE TO BACKUP before trying new things (there is no undo button for
macros):
Sub test()
Dim rngData As Range
Dim rngRow As Range
Dim rngDest As Range
Dim var1 As Variant
Dim var2 As Variant
Dim lngCount1 As Long
Dim lngCount2 As Long
Dim TotalCount As Long
Set rngData = Worksheets("Sheet1").Range("A1:B3") '<<CHANGE
Set rngDest = Worksheets("Sheet2").Range("A1") '<<CHANGE
For Each rngRow In rngData.Rows
var1 = Split(rngRow.Cells(1).Value, _
",", -1, vbTextCompare)
var2 = Split(rngRow.Cells(2).Value, _
",", -1, vbTextCompare)
For lngCount1 = LBound(var1) To UBound(var1)
For lngCount2 = LBound(var2) To UBound(var2)
rngDest.Offset(TotalCount, 0).Value = _
Trim(var1(lngCount1))
rngDest.Offset(TotalCount, 1).Value = _
Trim(var2(lngCount2))
TotalCount = TotalCount + 1
Next lngCount2
Next lngCount1
Next rngRow
End Sub
"CompleteNewb" wrote:
> This is worded very awkwardly, but I have examples below.
>
> I've been at this for a while, and have half-succeeded with several
> attempts, but nothing farther.
>
> I have a column with many rows of values, some of which are values separated
> by commas in the same cell. A second column has values related to the first
> column, but some of these are also multiple values separated by commas.
>
> I need to separate all cells that have multiple values separated by commas
> into separate rows, while still maintaining the relationship in the other
> column.
>
> For example, I currently have this:
>
> Dodge,Plymouth Trucks,Cars,Scooters
> Buick Cars
> GM,Ford Trucks,Cars
>
> What I need is this:
>
> Dodge Trucks
> Dodge Cars
> Dodge Scooters
> Plymouth Trucks
> Plymouth Cars
> Plymouth Scooters
> Buick Cars
> GM Trucks
> GM Cars
> Ford Trucks
> Ford Cars
>
> So you see, I need to de-concatenate each column, but still have all of the
> values in both still related to each other. And, as you see above, there
> are variable numbers of values separated by commas (some cells only have one
> value, some have 2, some 3, etc.).
>
> I can do this in either Access or Excel or both; I have done most of my
> experimenting using Excel's nice and easy Text to Columns utility, then
> importing into Access, but in building queries in Access or formulas in
> Excel I always either lose some data in one column or can't tie the values
> in both to each other to acheive the above desired output.
>
> Can anyone provide me with a formula or series of steps I need to do to
> accomplish this?
>
> Any help would be greatly appreciated, and thanks for reading.
>
>
>
>
>
Similar Threads
