Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create single column from multiple columns.
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. Ive 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!!! -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200905/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create single column from multiple columns.
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... "TraciAnn via OfficeKB.com" wrote: 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. Ive 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!!! -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200905/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Multiple Columns, Paste Under a Single column | Excel Worksheet Functions | |||
Create two columns from a single column with (first and last name) | Excel Discussion (Misc queries) | |||
Print multiple columns from single column data? | Excel Discussion (Misc queries) | |||
How do I convert multiple columns to a single column? | Excel Discussion (Misc queries) | |||
Single column into multiple columns | Excel Discussion (Misc queries) |