Create single column from multiple columns.

[TraciAnn via OfficeKB.com]

Column C has about 400 rows of unique numbers that represent locations.
8 other columns (M, Q, T, W, etc.) have numbers that represent contacts at
those locations.
Not all rows of the Contact columns have numbers (Null cells throughout).
Contact numbers may appear in multiple rows (for different locations)

I need to create a two column worksheet of the same workbook that lists
Location Numbers in one column and Contact numbers in the second column for
each occurrence of where a Contact number corresponds to a Location number.

Each Location number, in theory, could appear up to 8 times in the Location
column.
Each Contact number could appear two or three times in the Contact column.
NEVER should the Contact appear for the same Location a second time.

I’ve thought of just reproducing Column C 8 times in the first column and
then referencing each cell of each Contact column but that would produce many
blank cells where a location may only have one or two contacts.

What would be a more efficient way to produce these two columns?

Thanks in advance!!!

--

 

[Sean Timmons]

I can think of one way to do this anyways... takes a bit fo doing...

make a pivot from your table

Drop all 9 of your columns in the Row fields section, right clicking on each
to remove subtotals in field settings.

Then.. in a blank column to the right, first column will be =A5 to grab your
location number and the other would be =B5&", "&C5&", "&D5, etc.

Once done, highlight your 2nd column, do a find/replace.. look for " , , "
and replace with " , ".

Should get you pretty much there...