I am trying to work out how to write an intuitive formula in google-sheets that, given two tables (of booleans) with dimensions AxB, CxB, one generates the correlating table CxA following boolean OR.
What formula can be used to generate the table CxA? I definitely want the table to be filled using a single cell formula.
I have been thinking of using map, byrow, bycol or scan, but each time I start out I get lost in the matrix.
The tables below highlight the case where R is true for [a,e]; [1,2,3,6] are true for a; and [3,4,6] are true for e:- therefore [1,2,3,4,6] are true for R.
| AxB | R | B | G | K |
|---|---|---|---|---|
| a | 1 | 0 | 0 | 0 |
| b | 0 | 0 | 0 | 1 |
| c | 0 | 1 | 1 | 0 |
| d | 0 | 1 | 0 | 0 |
| e | 1 | 1 | 0 | 0 |
I am trying to work out how to write an intuitive formula in google-sheets that, given two tables (of booleans) with dimensions AxB, CxB, one generates the correlating table CxA following boolean OR.
What formula can be used to generate the table CxA? I definitely want the table to be filled using a single cell formula.
I have been thinking of using map, byrow, bycol or scan, but each time I start out I get lost in the matrix.
The tables below highlight the case where R is true for [a,e]; [1,2,3,6] are true for a; and [3,4,6] are true for e:- therefore [1,2,3,4,6] are true for R.
| AxB | R | B | G | K |
|---|---|---|---|---|
| a | 1 | 0 | 0 | 0 |
| b | 0 | 0 | 0 | 1 |
| c | 0 | 1 | 1 | 0 |
| d | 0 | 1 | 0 | 0 |
| e | 1 | 1 | 0 | 0 |
| CxB | 1 | 2 | 3 | 4 | 5 | 6 |
|---|---|---|---|---|---|---|
| a | 1 | 1 | 1 | 0 | 0 | 1 |
| b | 0 | 0 | 1 | 0 | 1 | 0 |
| c | 0 | 1 | 0 | 1 | 1 | 0 |
| d | 1 | 1 | 0 | 1 | 1 | 0 |
| e | 0 | 0 | 1 | 1 | 0 | 1 |
| CxA | 1 | 2 | 3 | 4 | 5 | 6 |
|---|---|---|---|---|---|---|
| R | 1 | 1 | 1 | 1 | 0 | 1 |
| B | 1 | 1 | 1 | 1 | 1 | 1 |
| G | 0 | 1 | 0 | 1 | 1 | 0 |
| K | 0 | 0 | 1 | 0 | 1 | 0 |
- 2 Please do not upload images of code/data/errors. Instead, provide a minimal, reproducible example using Tables or tools like Table to Markdown that can easily be copied and pasted. If necessary, as a last resort, Make an anonymous sample document. – Saddles Commented Feb 5 at 16:28
4 Answers
Reset to default 5Another approach using the virtues of MAKEARRAY:
=MAKEARRAY(COUNTA(B1:E1),COUNTA(B8:G8),LAMBDA(r,c,
ARRAYFORMULA(IF(SUM(INDEX(B2:E6,,r)*INDEX(B9:G13,,c)),1,0))))
You may try:
=index(sign(mmult(transpose(C3:F7),K3:P7)))
In C10 you can try:
=let(
head_rbgk,C2:F2,
tab_rbgk,C3:F7,
tab_123456,K3:P7,
map(B10:B13,lambda(x,
bycol(filter(tab_123456,filter(tab_rbgk,head_rbgk=x)),lambda(y,ifna(--or(y)))))))
Use map(), filter() and bycol(), like this:
=map(transpose(C2:F2), lambda(key, let(
col, filter(C3:F7, key = C2:F2),
matches, filter(K3:P7, col),
bycol(matches, lambda(m, n(or(m))))
)))
See map(), filter() and bycol().