merging multple columns into one column (dynamically)

[ssrvant]

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

 

[isabelle]

hi,

Dim MergeList(), i As Integer, c As Range, n As Integer
For i = 1 To Cells(1, Cells.Columns.Count).End(xlToLeft).Column
For Each c In Range(Cells(1, i))
n = n + 1
ReDim Preserve MergeList(n)
MergeList(n) = c
Next
Next
Sheets("MergeListSheet").[A1].Resize(UBound(MergeList) + 1) =
Application.Transpose(MergeList) '--->adapt name sheet

isabelle

Le 2014-07-25 12:29, ssrvant a écrit :

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.

 

[isabelle]

i forgot to tell you that i assume that each column was named according to the
title line (list1 list2 List3...)

isabelle

Le 2014-07-25 22:47, isabelle a écrit :