excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 510
- Office Version
- 365
- Platform
- Windows
hi!
can i reduce the formula in j4 to something i'm trying in j9?
i want to shorten the process cause i have a few thousands to test like this one
can i reduce the formula in j4 to something i'm trying in j9?
i want to shorten the process cause i have a few thousands to test like this one
test | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
2 | 1 | 2 | 3 | 4 | 5 | 22 | 1 | 2 | 3 | 4 | 5 | 6 | 15 | 16 | 17 | 18 | 22 | ||||
3 | 2 | 3 | 9 | 10 | 11 | 12 | |||||||||||||||
4 | 5 | 6 | 15 | 16 | 17 | 18 | 2 | ||||||||||||||
5 | reduce | ||||||||||||||||||||
6 | it | ||||||||||||||||||||
7 | to | ||||||||||||||||||||
8 | this? | ||||||||||||||||||||
9 | #VALUE! | ||||||||||||||||||||
test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K2:U2 | K2 | =LET(t,VSTACK(C2:H2,C4:H4),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))) |
J4 | J4 | =SUM(--(COUNTIF(K2#,C3:H3)>0)) |
J9 | J9 | =SUM(--(COUNTIF(LET(t,VSTACK(C2:H2,C4:H4),r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i)))),C3:H3)>0)) |
Dynamic array formulas. |