Search code examples
google-sheets

In Google Sheets, how to "combine" 2 cols, but make the result be the 1st col repeated with 2nd col data

In Col A, I have a list of names. e.g.

  Bob Jones
  John Doe
  Sally Smithers

In Col B, I have a list of dates. e.g.

  6/1/23
  6/2/23
  6/3/23

Both lists are dynamic and can have different #'s of rows.

I want to "combine" the cols but have the result be 2 columns with each name repeated and the dates repeated, like this:

Name Date
Bob Jones 6/1/23
Bob Jones 6/2/23
Bob Jones 6/3/23
John Doe 6/1/23
John Doe 6/2/23
John Doe 6/3/23
Sally Smithers 6/1/23
Sally Smithers 6/2/23
Sally Smithers 6/3/23

I do already have a formula for this that's been working: transpose(ARRAYFORMULA(TRIM(SPLIT(CONCATENATE(REPT(A2:A&"@@",count(B:B))), "@@"))))

But my lists are getting longer and I'm running into the "Text result of CONCATENATE is longer than the limit of 50000 characters" limit.

Is there a better way to do this that let's me do this with longer lists?

Solution

  • You can try with REDUCE:

    =REDUCE({"Name","Date"},TOCOL(A:A,1),LAMBDA(a,name,{a;BYROW(TOCOL(B:B,1),LAMBDA(date,{name,date}))}))

    enter image description here

    REDUCE does not concatenate the cells but iterates over a range and performs a cumulative action. Working with those curly brackets and BYROW you're able to just stack the cells, without joining and splitting