0

I have an excel file that has three columns - on column 1 I have a list of words, and on column 2 I have a list of attributes the prepended, while on column 3 I have a list of attributes to be appended.

How can I create a single list with all concatenations of attributes to be appended and prepended?

For example, I have words such as:

  • Cumin
  • Oregano
  • Turmeric

Prepend terms such as

  • recipes with
  • benefits of
  • substitute for

and Append terms such as

  • recipes
  • uses
  • powder

... and I would like to have an automated way of joining these to get:

  • recipes with cumin
  • benefits of cumin
  • substitute for cumin
  • cumin recipes
  • cumin uses
  • cumin powder
  • ...
Andy Mo
  • 13
  • 1
  • just to be clear, each of the three columns don't always need to be present. so the spice can have an appended AND/OR a prepended phrase (either or both)? – Bad_Neighbor Feb 09 '19 at 15:00
  • I have about 50 attributes to be prepended and 30 attributed to be appended. Each new line item created should be a result of appending OR prepending, not both. I am ok with generating two lists as well if it's easier - one of prepended words + attributes, and another of appended words + attributes. – Andy Mo Feb 09 '19 at 15:03
  • 1
    See https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-of-the-values-in-three-columns-in-excel – Gary's Student Feb 09 '19 at 15:27

1 Answers1

0
=ARRAYFORMULA({
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9),COUNTA(A1:A))),CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(B1:B&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(C1:C&CHAR(9),COUNTA(B1:B))),CHAR(9)))})

player0
  • 124,011
  • 12
  • 67
  • 124