Google-sheets – How to stack two columns in Google Sheet with column name in another column

google sheetsgoogle-apps-scriptgoogle-sheets-query

I have these columns in the Google Sheet.

Date        A   Date        B
1/1/2020    3   5/2/2021    328.28
1/2/2020    41  5/3/2021    322.75
1/9/2020    23  5/25/2021   342
1/12/2020   54  5/28/2021   333
1/15/2020   1   7/1/2021    320.82

What I want is to concatenate these two columns vertically with dates and have the columns header on another column.

Date        Value   Name
1/1/2020    3       A
1/2/2020    41      A
1/9/2020    23      A
1/12/2020   54      A
1/15/2020   1       A
5/2/2021    328.28  B
5/3/2021    322.75  B
5/25/2021   342     B
5/28/2021   333     B
7/1/2021    320.82  B

more of like python's stack operation. ​But want in the Google Sheet for dynamic update. I am looking for an equation that can do that with N number of columns and N number of rows.

Best Answer

You can do that with an { array expression }. To weed out blank rows, use query() like this:

=arrayformula( 
  query( 
    { 
      "Date", "Value", "Name"; 
      A2:B, left(B2:B, 0) & B1; 
      C2:D, left(D2:D, 0) & D1 
    }, 
    "where Col1 is not null", 
    1 
  ) 
)

If there are many columns in the source data, you can try the Array Expression Formula Builder spreadsheet that automates parts of the process.

Related Topic