Merging multiple columns into one column (dynamically)

  • Thread starter Thread starter mcghepa
  • Start date Start date
M

mcghepa

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
 
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
Click to expand...

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()-1>COUNTA($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.
 
Back
Top