1

I am interested in taking the values from 1 list in a sheet and concatenating it with the values from another list in a different sheet in Google Sheets. For example, if my lists are:

Sheet A

  • Apple
  • Orange

Sheet B

  • Quarter
  • Half
  • Whole

I would want to generate on Sheet C this...

  • Apple Quarter
  • Apple Half
  • Apple Whole
  • Orange Quarter
  • Orange Half
  • Orange Whole

Is this possible? I tried doing this with ARRAYFORMULA, but I just end up outputting something like this: Apple QuarterHalfWhole

kouki
  • 37
  • 3

1 Answers1

3

Here you go:

=ARRAYFORMULA(FLATTEN(A1:A2 & " " & TRANSPOSE(B1:B3)))
  • A1:A2 and B1:B3 indeed could be from a different sheet (tab or document (IMPORTRANGE will be used for that case))
  • FLATTEN is undocumented function in Google Sheets, which will make a 2D-range into a column. I learned about it recently from @MattKing here on SO.

enter image description here

kishkin
  • 5,152
  • 1
  • 26
  • 40
  • is it just me or there is no help/preview on this flatten fx? – player0 May 03 '20 at 01:31
  • 1
    @player0 same here. I thought at first that he used something scripted, but no, it is there. – kishkin May 03 '20 at 04:07
  • 4
    @player0 The "product experts" on the Google help forums have been discussing it with Google engineers. It was discovered by a user trying to create a custom function called "flatten" and the scripting tool wouldn't let him use that name. it was "reserved" for internal use. So we figured out what it did. I've reached out and heard back from them but don't feel comfortable sharing publicly what i've found out. here's the demo sheet i made for it. Feel free to copy or just share. https://docs.google.com/spreadsheets/d/196NDPUZ-p2sPiiiYlYsJeHD6F_eJq7CWO_hP7rFqGpc/edit?usp=sharing – MattKing May 03 '20 at 16:43