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