How to Combine two Lists In Excel

Question

I want to combine two lists into one, for example if we have

List A:
Item 1
Item 2
(empty)
Item 3

List B: 
Item 4
(empty)
Item 5
Item 6

from above two lists, want to combine them without any empty spaces such as:

Merged List should be:

Item 1
Item 2
Item 3
Item 4
Item 5
Item 6

How can we do this using one line of syntax in excel?enter image description here

Do you need to keep same values if they are present in the two lists, or all values are unique? — Michele, Aug 07 '21 at 09:54
Yes doesn't care about duplicates but empty cells should be avoided — John, Aug 07 '21 at 09:58
As far as I know to put together two columns and remove empty cells you would need to write a macro. But if you do not know how to do it, you may use an easy workaround. You copy List A and List B on the same column and then sort the list by selecting the whole column. Excel will sort all values and remove empty cells. Remind that value will be sorted (as in the reedited text here above, but not like the image you attached). — Michele, Aug 07 '21 at 10:08

score 0 · Answer 1 · answered Aug 07 '21 at 12:37

Assuming the original data is located in A1:B3 similar to picture shared. put :

C1 ->  =COUNTA(A$1:A1)
D1 ->  =COUNTA(B$1:B1)+MAX(C:C)
E1 ->  =IF(ROW()>MAX(D:D),"",IF(ROW()<MIN(D:D),INDEX(A:A,MATCH(ROW(),C:C,0)),INDEX(B:B,MATCH(ROW(),D:D,0))))

and drag all downwards.

Idea : use counta as the 'loader' index for index-match formula. column C & D can be placed elsewhere/hidden.

Please share if it works/understandable/not.

P.b · Answer 2 · 2021-08-07T13:12:02.480

if you need combine lists (of one column) you can calculate them using:

=IFERROR(
         IF(ROW()<=COUNTA($A$2:$A$5),
            INDEX($A$2:$A$5,
                  LARGE(IF($A$2:$A$5<>"",ROW($A$2:$A$5)-1),
                        COUNTA($A$2:$A$5)-ROW()+1)),
            INDEX($B$2:$B$5,
                  SMALL(IF($B$2:$B$5<>"",ROW($B$2:$B$5)-1),
                        -COUNTA($A$2:$A$5)+ROW())))
,"")

This should be entered with ctrl+shift+enter and dragged down.

This is based on List A being A2:A5 and List B being B2:B5

When using Office 365 one could use:

=LET(dataA,$A$2:$A$5,
          dataB,$B$2:$B$5,
          listA,FILTER(dataA,dataA<>""""),
          listB,FILTER(dataB,dataB<>""""),
          rowsA,ROWS(listA),
          rowsB,ROWS(listB),
          rowsAB,rowsA+rowsB,
          seqB,MOD(SEQUENCE(rowsAB,,rowsB-rowsA),rowsB)+1,
IF(SEQUENCE(rowsAB)<=rowsA,
     listA,
     INDEX(listB,seqB,)))"

which spills as the desired result and ranges are easily changeable.

score 0 · Answer 3 · answered Aug 07 '21 at 12:59

You can do it with a formula, but it is probably easier to do what @Michele suggested in the comments and just copy and paste on top of each other and sort.

For example, if you have the newest version of Excel:

=INDEX(
LET(x, $A$1:$A$5, y, $B$1:$B$5, z, $A$1:$B$5, myrows, SEQUENCE(ROWS(x)+ROWS(y),1),
myarray, INDEX(z, IF(myrows>ROWS(x), myrows-ROWS(x), myrows), IF(myrows>ROWS(x), 2,1)),
myhelper, IF(myarray=0, 0,1),
SORTBY(myarray, myhelper,-1)),
SEQUENCE(COUNTA($A$1:$B$5),1))

How to Combine two Lists In Excel

3 Answers3