Converting several columns containing lists of values into a single column

**komododragon** · 02-14-2009, 11:52 AM

Is it possible to automate this someow? I have many columns with lists of strings that I want to convert into a sequential list in a single column.

Any help would be appreciated.

**martindwilson** · 02-14-2009, 12:04 PM

in a new column enter= a1&","&b1&","c1 etc replacing , with whatever your seperator is, then drag down

**JBeaucaire** · 02-14-2009, 01:28 PM

Martin's idea is a good one if you're looking to convert like so:

Please Login or Register  to view this content.

But if you want this:

Please Login or Register  to view this content.

...then this is formula you need in column D starting at D2:

=SMALL($A$2:$C$4,ROW()-1)

...and copy that down. If you start at a different row, then you need to adjust the -1 at the end to get the first value properly. This works for organizing numbers.

If you want this same thing but with text, my only guess is a quick macro to merge all the columns into a single column the way the formula above is doing for the numbers.

Post your question in the Excel Programming forum if that is what you want. Be sure to include clear explanation of the data layout, including columns, starting ranges, what/where you want the final single column to be. You get the idea.