Re-structuring contact lists

[ryan.marples]

So I've got a long Excel workbook in the following structure:

AAA Widgets,John Smith,555-555-5555,Bob Smith,666-666-6666,Bill
Smith,777-777-7777
BBB Widgets,Jane Doe,111-111-1111,John Doe,222-222-2222,Mary
Doe,333-333-3333
CCC Widgets,Jim Bo,444-444-4444,John Bo,888-888-8888,Chuck
Bo,999-999-9999

I would like to put this in the following format:

AAA Widgets,John Smith,555-555-5555
AAA Widgets,Bob Smith,666-666-6666
AAA Widgets,Bill Smith,777-777-7777
BBB Widgets,Jane Doe,111-111-1111
BBB Widgets,John Doe,222-222-2222
BBB Widgets,Mary Doe,333-333-3333
CCC Widgets,Jim Bo,444-444-4444
CCC Widgets,John Bo,888-888-8888
CCC Widgets,Chuck Bo,999-999-9999

I have traditionally imported the Excel file into a database and wrote
some queries that re-mapped the data but I would love to be able to do
this in Excel itself. Any ideas? I have played around with the OFFSET
function but haven't been able to come up with anything quite yet...

Any ideas much appreciated!

R

 

[Max]

One play which might work provided
each source line always contain 3 contacts

Sample construct available at:
http://www.savefile.com/files/3321670
ReStructuringContactLists_ryan_gen.xls

Assuming source data is in A1 down,
with empty cols to the right

Use Data > Text to Columns (delimited, comma)
to split col A into cols A to G

Reconcat the 3 names & contact nos in cols H to J

Put in
H1: =B1&","&C1
I1: =D1&","&E1
J1: =F1&","&G1
Select H1:J1, fill down

Then put in K1:
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/3),)&","&
OFFSET($H$1,INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))
Copy down until the extracted data is exhausted

Col K returns the required results

Freeze the values in col K with an in-place:
Copy > Paste special > check "Values" > OK

 

[Ryan M]

Thank you very much Max for the ideas and the nice sample workbook. I
adapted those formulas and got it working. Much appreciated!

 

[Max]

You're welcome, Ryan !
Thanks for the feedback