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.

  • Welcome to Web Applications Stack Exchange. May 25, 2021 at 12:26
  • There are a lot of factors to consider here if you truly want flexibility (e.g., how will the number of columns and rows be determined? is the max range set or flexible? will you use cells to input rows/columns desired, or will you edit the formula each time, etc.?). Share a link to a sample spreadsheet, being sure to set the link's Share permission (when you create the link) to "Anyone with the link..." and "Editor." Comment below when you've done that, and I'll take another look.
    – Erik Tyler
    May 25, 2021 at 18:16
  • I have kept this post open in a separate browser window, checking to see if you've replied with more information and a link, but you have not responded. I will keep the post open for another 12 hours, after which I will assume that you no longer want the additional help on this.
    – Erik Tyler
    May 27, 2021 at 6:45
  • Hey, I think following answer is something close-by.. But what I really want simple, any colunms or rows like above to stack over each other ... Why a spreadsheet required to be share.. I don't care where it started. my question, for n-column with n-row, can we translate values to 3 rows stack column.. one for date, one for values and one for the unique column name..
    – Alfi
    May 28, 2021 at 2:40

1 Answer 1


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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.