This question is a variation of this one but with an additional "step". This time I'm working on a glossary in Google Sheets, where each row contains a list of related terms in two columns: English terms in column A and Portuguese terms in column B. Semicolons separate the synonym terms in each column. Also, there are other columns with content related to those terms, like "Comments" in column C, "Validation" in column D, and "Field" in column E. For example:

This question is a variation of this one but with an additional "step". This time I'm working on a glossary in Google Sheets, where each row contains a list of related terms in two columns: English terms in column A and Portuguese terms in column B. Semicolons separate the synonym terms in each column. Also, there are other columns with content related to those terms, like "Comments" in column C, "Validation" in column D, and "Field" in column E. For example:

I need to output all possible combinations (like a Cartesian product) of the terms from column A and column B within the same row in separate rows while keeping the content of the other rows for their respective terms. The result for the above example should be like this:

Can you help me with a formula or any other method to achieve that in Google Sheets?

Previously, @doubleunary helped me with this Google Sheets formula:

=let( 
  get_, lambda(a, filter(a, len(A2:A), len(B2:B))), 
  split_, lambda(a, split(a, "; ", false)), 
  pairs, map(get_(A2:A), get_(B2:B), lambda(en, es, 
    torow(sort(split_(en) & "→" & tocol(split_(es)))) 
  )), 
  sort(split(tocol(pairs, 1), "→")) 
)

But now, besides the idiom columns, there are other columns which I tried to include/adapt into the code above, but without success.

• google-sheets
• google-sheets-formula

• In the expected outcome, the "Comments" column shows "cancer type" for many items but "cancer type" is not included anywhere in the sample data. Conversely, "illness" is included in the sample data but doesn't appear at all in the expected results. – Tedinoz Commented Mar 17 at 9:42
• You have referenced another question - This question is a variation of - as well as a formula related to that question. Each question needs to stand on its own; if the referenced question is important/relevant, then you need to explain how/why that question is relevant, summarise that question and any answer(s) in this question. – Tedinoz Commented Mar 17 at 9:49
• 1 Related question: How do I cross join two, multi-column tables in Google Sheets — without string hacking or Apps Script? – Tedinoz Commented Mar 17 at 9:51

1 Answer 1

Here's a generalized approach which you may adapt accordingly:

=reduce(A1:E1,A2:index(A:A,match(,0/(A:A<>""))),lambda(a,c,vstack(a,reduce(tocol(,1),split(c,"; ",),lambda(f,q,vstack(f,
 let(Σ,tocol(iferror(split(index(B:B,row(c)),"; ",))),Λ,lambda(x,chooserows(x,sequence(rows(Σ),1,1,0))),hstack(Λ(q),Σ,Λ(index(C:E,row(c)))))))))))