De-concatenate but maintain relationship/record structure

[CompleteNewb]

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.

 

[Guest]

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

 

[NickHK]

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]

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