excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 528
- Office Version
- 365
- Platform
- Windows
hi,
any way to implment this formula =CountUnique(A2:D2,A3:D3)
(right now the formula check how many repeats there is but only against one row)
but in a larger range like A2:D2, A3:D1000
but to check each 4 cells individual, like a4:d4 if the result is 4 (and only 4) repeating numbers, in any order,
and then a5:d5 and so on,
and returns only the number of times when the result is 4?
in simpler words.... looking for is how many full combinations (meaning 4 numbers repeats) of 2-7-7-8 (example only) there's in a range of 1000 rows, but checking it per row?
maybe using the column helper by perterSS ?
=SMALL(MID(B2,{1,2,3,4},1)+0,1)&SMALL(MID(B2,{1,2,3,4},1)+0,2)&SMALL(MID(B2,{1,2,3,4},1)+0,3)&SMALL(MID(B2,{1,2,3,4},1)+0,4)
even try it myself when q2 is the helper, but for some reason it didn't worked
=IF(COUNTIF(q$1:q1,B2)=0,IF(COUNTIF($q$2:$q$10000,b2)=1,0,COUNTIF($q$2:$q$10000,b2)),"-")
example:
side question:
when pressing ctrl+shift+down for selecting entire column, for instance in column b,
while col a has 1000 rows,
sometimes it goes down and select up to a million rows!
and i don't have any blank rows
why is that?
any way to implment this formula =CountUnique(A2:D2,A3:D3)
(right now the formula check how many repeats there is but only against one row)
but in a larger range like A2:D2, A3:D1000
but to check each 4 cells individual, like a4:d4 if the result is 4 (and only 4) repeating numbers, in any order,
and then a5:d5 and so on,
and returns only the number of times when the result is 4?
in simpler words.... looking for is how many full combinations (meaning 4 numbers repeats) of 2-7-7-8 (example only) there's in a range of 1000 rows, but checking it per row?
maybe using the column helper by perterSS ?
=SMALL(MID(B2,{1,2,3,4},1)+0,1)&SMALL(MID(B2,{1,2,3,4},1)+0,2)&SMALL(MID(B2,{1,2,3,4},1)+0,3)&SMALL(MID(B2,{1,2,3,4},1)+0,4)
even try it myself when q2 is the helper, but for some reason it didn't worked
=IF(COUNTIF(q$1:q1,B2)=0,IF(COUNTIF($q$2:$q$10000,b2)=1,0,COUNTIF($q$2:$q$10000,b2)),"-")
example:
| q.xlsm | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | -1 | -2 | -3 | -4 | -5 | -6 | -7 | -8 | -9 | -10 | -11 | -12 | -13 | -14 | -15 | -16 | -17 | -18 | -19 | -20 | how many time 4's | ||||||
| 2 | 8 | 7 | 7 | 2 | 1 | 4 | 1 | 3 | 3 | ||||||||||||||||||
| 3 | 8 | 4 | 6 | 4 | 1 | 1 | 0 | 1 | 1 | ||||||||||||||||||
| 4 | 7 | 7 | 8 | 2 | 1 | 3 | 4 | 1 | |||||||||||||||||||
| 5 | 3 | 2 | 2 | 4 | 1 | 1 | 1 | 1 | |||||||||||||||||||
| 6 | 7 | 5 | 2 | 7 | 3 | 0 | 1 | 1 | |||||||||||||||||||
| 7 | 7 | 2 | 8 | 7 | 1 | 1 | 2 | 3 | |||||||||||||||||||
| 8 | 8 | 6 | 4 | 4 | 1 | 1 | 0 | 1 | |||||||||||||||||||
| 9 | 3 | 1 | 6 | 7 | 1 | 1 | 1 | 1 | |||||||||||||||||||
| 10 | 8 | 3 | 8 | 2 | 1 | 2 | 2 | 1 | |||||||||||||||||||
| 11 | 5 | 7 | 7 | 2 | 1 | 3 | 0 | 0 | |||||||||||||||||||
| 12 | 5 | 8 | 8 | 1 | 1 | 0 | 0 | 0 | |||||||||||||||||||
| 13 | 7 | 7 | 8 | 2 | 0 | 0 | 0 | 0 | |||||||||||||||||||
| 14 | 4 | 3 | 4 | 6 | 0 | 0 | 0 | 0 | |||||||||||||||||||
test | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E14 | E2 | =CountUnique(A2:D2,A3:D3) |
| F2:F14 | F2 | =CountUnique(A2:D2,A4:D4) |
| G2:G14 | G2 | =CountUnique(A2:D2,A5:D5) |
| H2:H14 | H2 | =CountUnique(A2:D2,A6:D6) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| Y1:Y3 | Cell Value | =4 | text | NO |
side question:
when pressing ctrl+shift+down for selecting entire column, for instance in column b,
while col a has 1000 rows,
sometimes it goes down and select up to a million rows!
and i don't have any blank rows
why is that?
