1

In Excel, I'm combining large lists using TRANSPOSE then CONCATENATE. My lists are >400 names and not always organized by column.

When I hit "F9" Excel has started adding a ";" between all of my values instead of "," thereby breaking the function. Has anyone else seen this? Know how to fix this? If I manually replace the semicolon, it works again.

Example data:enter image description here

If the names are vertical/organized by column

=TRANSPOSE(A1:A12)&", "

The functional output is

={"John, ","Tom, ","Mary, ","Jackson, ","Rob, ","Gerry, ","Heidi, ","Sheila, ","Alison, ","Wendy, ","Laura, ","Marion, "}

If the names are horizontal/organized by row

 =TRANSPOSE(A1:L1)&", "

The non-functional output is

={"John, ";"Tom, ";"Mary, ";"Jackson, ";"Rob, ";"Gerry, ";"Heidi, ";"Sheila, ";"Alison, ";"Wendy, ";"Laura, ";"Marion, "}

LG2
  • 11
  • 2
  • Please share some of your sample data.. and what you had tried.. like these [Example1](https://stackoverflow.com/questions/51462803/formula-to-combine-similar-rows-but-sum-values-from-1-column/51462879) [Example2](https://stackoverflow.com/questions/51462803/formula-to-combine-similar-rows-but-sum-values-from-1-column/51462879) [Example3](https://stackoverflow.com/questions/51449555/excel-sumifs-checking-if-a-column-contains-text/51451498) – p._phidot_ Jul 25 '18 at 16:01
  • @p._phidot_ question updated with examples, thanks – LG2 Jul 25 '18 at 16:36

1 Answers1

0

If the names are vertical (A1:A12). Put

A2       =A1&","
A3       =A2&B3

and drag both until L3. A3 is your result.

If the names are horizontal (A1:L1), put

B1       =A1&", "   
C1       =B1&C2

and drag both until C12. C1 is your result.

Hope that solves. (:

p._phidot_
  • 1,913
  • 1
  • 9
  • 17