DE-Concatenate but maintain relationship and record structure

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 achieve the above desired output.

Can anyone provide me with a function or series of steps I need to do to
accomplish this?

Any help would be greatly appreciated, and thanks for reading.


.

Relevant Pages

  • Re: Looking up values in a column of unordered values by interpolation
    ... unordered, and there is no exact match between these columns, 4) The ... as long as excel produces one of the correct answers. ... too many brain cells attempting it on my own. ... separate sheet, and to make the process easier you could record a simple ...
    (microsoft.public.excel)
  • Re: Vertical alignment issue
    ... If you do in Excel what you are trying to do in VBA, ... results (ie, all cells are left-aligned). ... > capable of individual adjustment. ... > Why would Microsoft have separate controls if you can't use them? ...
    (microsoft.public.excel.programming)
  • Re: Separating text in a field
    ... You don't need to do the tab thing. ... > would find all commas and replace it with a tab. ... Open it in Excel and when the dialog box asks you for the delimiter, ... All the field headers will be in separate cells. ...
    (microsoft.public.access.gettingstarted)
  • Re: Merging cells
    ... just recently transfered the info from Word to Excel. ... Firstly merging cells is a bad idea. ... Indeed you would want separate data if you want to do a mail merge. ... Have a look at my information sheet at ...
    (microsoft.public.excel.newusers)
  • Re: search of a string in a cell and counting occurences using COUNTIF
    ... To separate a column on a delimiter you can select the ... Microsoft MVP - Excel ... > i need to separate out items which are in a column with cells containing ...
    (microsoft.public.excel)