How to get array with values into one column-CodePudding

In excel I have two columns (A and B) filled with text values and I want to get all the values from the two column into one column (C) and it needs to be dynamic

example:

Column A: {car, plane, boat}
Column B: {bike, motor}
Column C: {car, plane, boat, bike, motor}

I hope someone can help me!

CodePudding user response：

use:

=QUERY(FLATTEN(A:C); "where Col1 is not null")

CodePudding user response：

This will work in both Excel(any version) and Google Sheets: Put this in C1 and copy down till you have enough cells filled to capture both lists maximum lengths:

=INDEX(A:B,IF(ROW($ZZ1)>COUNTA(A:A),ROW($ZZ1)-COUNTA(A:A),ROW($ZZ1)),IF(ROW($ZZ1)>COUNTA(A:A),2,1))&""

With Office 365 Excel:

=LET(
    rng1st,A:A,
    rng2nd,B:B,
    cnt1st,COUNTA(rng1st),
    cnt2nd,COUNTA(rng2nd),
    sq,SEQUENCE(cnt1st cnt2nd),
    INDEX(CHOOSE({1,2},rng1st,rng2nd),IF(sq>cnt1st,sq-cnt1st,sq),IF(sq>cnt1st,2,1)))

Put that in C1 and the results will spill automatically:

CodePudding user response：

Google Sheets has a FLATTEN function which allows you to do this, but there is no equivalent function in Excel.

There is a workaround though, which does combines the 2 columns in the order of their rows:

This works in Excel Version 2019 or later

=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,A:B)&"</b></a>","//b")

CodePudding user response：

=IF(SEQUENCE(ROWS(range1) ROWS(range2))<ROWS(range1) 1,range1,INDEX(range2,MOD(SEQUENCE(ROWS(range1) ROWS(range2),,ROWS(range2)-ROWS(range1)),ROWS(range2)) 1,SEQUENCE(1,COLUMNS(range2)))) This will stack range2 under range1 in Excel (365) this also works for ranges with 2 columns, or more