De-concatenate but maintain relationship/record structure
 

Awesome.

I found a different way to do it in Access after exporting, but I very much
appreciate the posts here to help me. You guys are awesome.

Long live the actually helpful people on the Internet! You all deserve palm
frond fanning and peeled grape eating!

Complete Newb



"NickHK" wrote in message
...
Someone may have a worsheet/array formula solution, but in VBA.
Assumes you first column of data is named "rngData":

Private Sub CommandButton1_Click()
Dim temp1 As Variant
Dim temp2 As Variant
Dim cell As Range
Dim i As Long
Dim j As Long
Dim EntryCount As Long

For Each cell In Range("rngData")
temp1 = Split(cell.Value, ",")
temp2 = Split(cell.Offset(0, 1).Value, ",")
For i = 0 To UBound(temp1)
For j = 0 To UBound(temp2)
Range("D1").Offset(EntryCount, 0).Resize(1, 2).Value =
Array(temp1(i), temp2(j))
EntryCount = EntryCount + 1
Next
Next
Next

End Sub

NickHK

"CompleteNewb" wrote in message
. ..
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.