CODEWITHSUNDEEP
arraysexcelgoogle-sheetsexcel-formula
milan1610
milan1610

Reputation: 13

How to get array with values into one column

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!

Upvotes: 1

Views: 1493

Answers (5)

R.bora
R.bora

Reputation: 59

If the amount of the rows won't be changed

=IFERROR(INDEX($A$1:$A$3, ROWS(C1:$C$1)), IFERROR(INDEX($B$1:$B$2, ROWS(C1:$C$1)-ROWS($A$1:$A$3)), ""))

ctrl + shift + enter

If the amount of the rows can be changed

=IFERROR(INDEX(INDIRECT("$A$1:$A$"&COUNTIF(A:A,"")), ROWS(C1:$C$1)), IFERROR(INDEX(INDIRECT("$B$1:$B$"&COUNTIF(B:B,"")), ROWS(C1:$C$1)-ROWS(INDIRECT("$A$1:$A$"&COUNTIF(A:A,"*")))), ""))

ctrl + shift + enter

Upvotes: -1

Gangula
Gangula

Reputation: 7334

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")

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152585

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))&""

enter image description here

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:

enter image description here

Upvotes: 2

P.b
P.b

Reputation: 11588

=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

Upvotes: 0

player0
player0

Reputation: 1

use:

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

Upvotes: 2

Related Questions