Sunday, March 19, 2017

yogi_Create A List Of Words By Combining Words From 3 Columns

Google Spreadsheet   Post  #2131
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-19-2017
question by Guilherme Avila:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/6WLSmO1Aj0I;context-place=forum/docs
Combination of list of words - Build an Array
Hi,

I am trying to build an array with 3 columns:

Column A:
cat
dog
cow
Column B:
1
2
3
Column C:
A
B
C

I've found this topic here that explains how to do with 2 columns.

So with 2 columns, I have already done it. You can see in this public Spreadsheet Here.

With 2 columns, I am using this formula:

=ArrayFormula(TRANSPOSE(SPLIT( CONCATENATE( A1:A3 &TRANSPOSE( B1:B3 & CHAR(9))) ; CHAR(9))))

And my result is:

cat1
cat2
cat3
dog1
dog2
dog3
cow1
cow2
cow3

Fine. But I need help to go further in the formula and make it work with the third column.

I expect the end result to be like this:

cat1A
cat1B
cat1C
cat2A
cat2B
cat2C
cat3A
cat3B
cat3C
dog1A
dog1B
dog1C
dog2A

...


Thanks for your help.

Guilherme.