Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple columns into one column (dynamically)
I am looking to merge multiple columns into one list. I have some code that will allow me to merge three columns into one list, but I am looking to be able to merge more than three. below is an example of what I am trying to do, and below that is the code that I am currently using for merging three columns.
list1 list2 List3 merged list AA AB AC AA AB AC =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)), IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)), ""))) + CTRL + SHIFT + ENTER |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging multiple columns into one column (dynamically)
I am looking to merge multiple columns into one list. I have
some code that will allow me to merge three columns into one list, but I am looking to be able to merge more than three. list1 list2 List3 merged list AA AB AC AA AB AC Below is one automatic way, if these assumptions hold: - Each list has at least one entry. - The lists are contiguous. - No more than 10 lists. - No more than 50 entries in each list. (If the 10 or 50 limit is too small, it is straightforward to increase the limits.) The merged list will be in column M. Columns Q & R are helpers. They're the column number and row number of the cell to fetch for that row of merged list. First, put 1 in Q2 and 1 in R2. Then put in Q3: =IF(ROW()-1COUNTA($A$2:$J$51), "", Q2+ (COUNTA(OFFSET($A$1,1,Q2-1,50,1))=COUNTIF($Q$2:$Q2,Q2))) Then put in R3: =IF(Q3="","",IF(Q3=Q2,R2+1,1)) Then put in M2: =IF(Q2="","",INDEX($A$2:$J$51,R2,Q2)) and copy this into M3. Then select M3:R3 and copy down to row 501. (That's the longest possible merged list: 10 lists times 50 entries, plus one for the header row.) Screen shot: zvkmpw.home.comcast.net/merging_columns_dynamically.JPG Columns Q & R can be hidden. Hope this helps getting started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging multple columns into one column (dynamically) | Excel Programming | |||
opposite of Merging data in multiple columns into one | Excel Programming | |||
Merging multiple columns of data into one column of data | Excel Discussion (Misc queries) | |||
Macro to Combine 2 columns to make one column without merging | Excel Discussion (Misc queries) | |||
Merging duplicate data across multiple columns | Excel Programming |