google-sheetsgoogle-sheets-formula

How to List All Combinations of Values from Two Semicolon-Separated Columns in Google Sheets While Keeping Other Columns Content?


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:

English Portuguese Comments Validation Field
accrued income renda acumulada; receita acumulada Wikipedia finance
Chronic Lymphocytic Leukemia; CLL leucemia linfocítica crônica; LLC ilness medical dictionary medicine
small capital; small cap versalete gender: masculine client tipography

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:

English Portuguese Comments Validation Field
accrued income renda acumulada Wikipedia finance
accrued income receita acumulada Wikipedia finance
Chronic Lymphocytic Leukemia leucemia linfocítica crônica cancer type medical dictionary medicine
Chronic Lymphocytic Leukemia LLC cancer type medical dictionary medicine
CLL leucemia linfocítica crônica cancer type medical dictionary medicine
CLL LLC cancer type medical dictionary medicine
small cap versalete gender: masculine client tipography
small capital versalete gender: masculine client tipography

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.


Solution

  • 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)))))))))))
    

    enter image description here