0

If column A contains

blue
red
green
yellow

and column B contains

fish
dog
cat
horse

what would be a formula to put the following in column C?

bluefish
bluedog
bluecat
bluehorse
redfish
reddog
redcat
redhorse
greenfish
greendog
greencat
greenhorse
yellowfish
yellowdog
yellowcat
yellowhorse

I looked at using a PivotTable, but it didn't work using columns from different sheets in the document.

J Cornelius
  • 23
  • 4
  • 2
    Does this answer your question? [Is there a way to perform a cross join or Cartesian product in excel?](https://stackoverflow.com/questions/26999604/is-there-a-way-to-perform-a-cross-join-or-cartesian-product-in-excel) – Tim Biegeleisen Nov 21 '19 at 17:41

2 Answers2

0

If you want to do this with native Excel formula then try below approach

Assumption: Data in cells A1:A4 and B1:B4 respectively and formula being written in cell C1.

=INDEX($A$1:$A$4,CEILING(ROW()/4,1))&INDEX($B$1:$B$4,MOD(ROW()-1,4)+1)

Copy down as much as needed.

Note: Depending on your actual layout some of the parameters will need adjustments.

Edit

In cell D1 insert formula =MATCH("zz",A:A,1).

In cell D2 insert formula =MATCH("zz",B:B,1)

In cell C1 insert following formula

=IFERROR(INDEX($A$1:INDEX(A:A,$D$1),CEILING(ROW()/4,1))&INDEX($B$1:INDEX(B:B,$D$2),MOD(ROW()-1,4)+1),"")

Copy up to maximum number of rows you expect the combinations to occur.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • Is there a way to make this work with an unknown number of cells in each column? We are trying to make a reusable template. – J Cornelius Nov 21 '19 at 18:14
0

Got it working with the help of @shrivallabha.redij. Here's the formula that worked for me:

=IFERROR(INDEX($A$1:INDEX(A:A,$D$1),CEILING(ROW()/$D$2,1))&INDEX($B$1:INDEX(B:B,$D$2),MOD(ROW()-1,$D$2)+1),"")

The difference was using the value of $D$2 for the row calculation.

J Cornelius
  • 23
  • 4