3

I have two columns, one where each cell contains a comma-separated list of first names and the second column where each cell contains a comma-separated list of second names. I need to "zipper" the two columns into a third column containing a comma-separated list of full names.

For example:

Column A                  Column B                 Column C (formula?)
Joe, Frank, Billy         Bloggs, Spencer, Bragg   Joe Bloggs, Frank Spencer, Billy Brag
Martin, Michael, Charlie  Sheen, Sheen, Sheen      Martin Sheen, Michael Sheen, Charlie Sheen

Unfortunately I cannot use Macros, the only option I can use is some kind of formula but nothing springs to mind.

Is this an impossible formula to write?

Many thanks for the help!

AlanT
  • 31
  • 4
  • 2
    Is it always in groups of three? – Scott Craner Jan 22 '16 at 16:26
  • No, it can be one to N depending on how many subrecords have been rolled up and concatenated into the cells. Only rule is that it's the same number for corresponding cells in both columns. – AlanT Jan 22 '16 at 16:36
  • Then the answer is most likely not possible without vba. – Scott Craner Jan 22 '16 at 16:37
  • 4
    You could consider another approach: see this question: http://superuser.com/questions/483419/how-to-split-a-string-based-on-in-ms-excel/483422 for a different way of looking at this problem ;) (ie split the data up first, then figure out a formula for handling that resulting data ;) ) – Ditto Jan 22 '16 at 16:38
  • 1
    You may find this discussion helpful - in short, there are Array Formulas which allow multiple calculations to be completed and then combined into a single answer, but it is often not possible to combine text data in this way. http://stackoverflow.com/q/33112330/5090027 In your case I suggest as @Ditto said to break your data out into new columns by first/last name and create a new name column, then 'zip' them back up. You'll either need VBA or a lot of helper columns to make this work. – Grade 'Eh' Bacon Jan 22 '16 at 18:48
  • Thanks very much folks. Looks like I will need to go back to the vendor and ask them to create a new column in the workbook that they produce. Unfortunately I cannot use macros in the spreadsheet. – AlanT Jan 23 '16 at 16:37

1 Answers1

1

In D1 through I1 enter the following formulas: 

=TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
=TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",999)),COLUMNS($A:B)*999-998,999))
=TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",999)),COLUMNS($A:C)*999-998,999))
=TRIM(MID(SUBSTITUTE($B1,", ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
=TRIM(MID(SUBSTITUTE($B1,", ",REPT(" ",999)),COLUMNS($A:B)*999-998,999))
=TRIM(MID(SUBSTITUTE($B1,", ",REPT(" ",999)),COLUMNS($A:C)*999-998,999))

Then in C1 enter:

=D1 & " " & G1 & ", " & E1 & " " & H1 & ", " & F1 & " " & I1

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99