=SORT(UNIQUE(FILTER(B2:B20,B2:B20<>"")))
=INDEX($B$3:$B$20,MATCH(0,COUNTIF($E$1:E1,$B$3:$B$20),0))
Book1 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | |||||||||||||
2 | Rank | Unique Count | Extract | MAX C | |||||||||
3 | Abcde | 0 | 9 | 8 | aaaa1 | 2 | |||||||
4 | ase45 | 2 | 11 | aabb22 | 1 | ||||||||
5 | bb234 | 1 | 13 | Blank Count | Abcde | 0 | |||||||
6 | ss123 | 3 | 15 | 7 | akbk2 | 1 | |||||||
7 | aabb22 | 1 | 8 | ase45 | 3 | ||||||||
8 | 18 | bb234 | 2 | ||||||||||
9 | ase45 | 3 | 11 | ss123 | 3 | ||||||||
10 | zde4 | 1 | 16 | zde4 | 5 | ||||||||
11 | bb234 | 2 | 13 | ||||||||||
12 | zde4 | 5 | 16 | ||||||||||
13 | akbk2 | 1 | 10 | ||||||||||
14 | aaaa1 | 2 | 7 | ||||||||||
15 | 18 | ||||||||||||
16 | 18 | ||||||||||||
17 | 18 | ||||||||||||
18 | 18 | ||||||||||||
19 | 18 | ||||||||||||
20 | 18 | ||||||||||||
21 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G3 | G3 | =SUM(--(FREQUENCY($E$3:$E$20,$E$3:$E$20)>0))-(G6>0) |
G6 | G6 | =COUNTBLANK($B$3:$B$20) |
J3:J10 | J3 | =MAXIFS($C$3:$C$20,$B$3:$B$20,I3) |
I3:I14 | I3 | =IF(ROWS($I$3:I3)>$G$3,"",INDEX($B$3:$B$20,MATCH(MIN(IF(ISNA(MATCH($B$3:$B$20,$I$2:I2,0)),$E$3:$E$20)),$E$3:$E$20,0))) |
I15:I18 | I15 | =IF(ROWS($I$1:I13)>11,"",INDEX($B$3:$B$20,MATCH(MIN(IF(ISNA(MATCH($B$3:$B$20,$I$1:I13,0)),$E$3:$E$20)),$E$3:$E$20,0))) |
E3:E20 | E3 | =IF($B$3:$B$20="",ROWS($B$3:$B$20),MMULT(--(B3:$B$20>TRANSPOSE($B$3:$B$20)),ROW($B$3:$B$20)^0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Please put that in your account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)I have Office 365