Link to home
Start Free TrialLog in
Avatar of svillardi
svillardi

asked on

Separate cells into rows based on multiple entries in the cell

Hi Experts:

Let's say I have a table with multiple entries like this:
 
NameAddressesContacts
JohnMain Street
 Elm Street
 Oak Street
Plumber
 Electrician
MaryFirst Street
 Second Street
 Third Street
Carpenter
 Roofer

How can I programmatically change the table so there's one entry per cell, like this:
 
NameAddressesContacts
JohnMain StreetPlumber
JohnMain StreetElectrician
JohnElm StreetPlumber
JohnElm StreetElectrician
JohnOak StreetPlumber
JohnOak StreetElectrician
MaryFirst StreetCarpenter
MaryFirst StreetRoofer
MarySecond StreetCarpenter
MarySecond StreetRoofer
MaryThird StreetCarpenter
MaryThird StreetRoofer



Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

If you paste your data over the blue table, and refresh the green table you should get what you are looking for.

EEMerge.xlsx
Avatar of Norie
Norie

svillardi

In the Addresses and Contacts are there multiple values in single cells?
Avatar of svillardi

ASKER

@Tom Ferrar - The data I posted is an example.  What I have is a couple of dozen columns and hundreds of rows.  Will your solution work?

@Norie - yes.

Thank you both.
Don't think so.  The solution posted came from Power Query.  Most likely, it could be tweaked to work for the larger set.  
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Could you provide a sample workbook with the names changed if necessary?
@Norie I do not have Power Query at the moment, but am transferring to another computer with MSFT365 very shortly.  Marking this as correct.  Will revisit this later.  Thank you!