Google Sheets- How to concat each item in column 1 with each and every item in column 2

2
votes

In a google spreadsheet, I have 2 columns of data. I'd like to create a third column that concatenates each item in column A with every and each item in column B, and add a space between the two values.

Result I'm trying to get:

Column A
Red
Blue

Column B
Square
Circle
Rectangle

Column C Required Result
Red Square
Red Circle
Red Rectangle
Blue Square
Blue Circle
Blue Rectangle

I've been trying various concatenations within ArrayFormula but can't get a formula that works as required. Any help would be much appreciated.

1
google-sheets

1 Answers

1
votes

Please try:

=transpose(split(textjoin("|",1,array_constrain(ArrayFormula(A2:A4&" "&transpose(B2:B4)),2,3)),"|"))