faizee.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | S# | fruits | |||
2 | 1 | apple | |||
3 | 1 | mango | |||
4 | 2 | apple | |||
5 | 1 | apple | |||
6 | 2 | mango | |||
7 | 3 | jelly | |||
8 | 2 | jelly | |||
9 | 3 | apple | |||
10 | 2 | pinnable | |||
11 | 3 | mango | |||
12 | 2 | pinnable | |||
13 | 2 | pinnable | |||
14 | |||||
15 | |||||
16 | S No | 2 | |||
17 | apple | ||||
18 | mango | ||||
19 | jelly | ||||
20 | pinnable | ||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B17:B20 | B17 | =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$13)/($A$2:$A$13=$C$16)/ISNA(MATCH($B$2:$B$13,$B$16:$B16,0)),1)),"") |
faizee.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | S# | fruits | Rank | ||
2 | 1 | apple | |||
3 | 1 | mango | |||
4 | 2 | apple | 6 | ||
5 | 1 | apple | |||
6 | 2 | mango | 4 | ||
7 | 3 | jelly | |||
8 | 2 | jelly | 5 | ||
9 | 3 | apple | |||
10 | 2 | pinnable | 3 | ||
11 | 3 | mango | |||
12 | 2 | pinnable | |||
13 | 2 | pinnable | |||
14 | |||||
15 | |||||
16 | S No | 2 | |||
17 | apple | ||||
18 | apple | ||||
19 | jelly | ||||
20 | mango | ||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C13 | C2 | =IF(A2=C$16,IF(COUNTIFS(A$2:A2,C$16,B$2:B2,B2)=1,COUNTIFS(A$2:A$13,A2,B$2:B$13,">="&B2),""),"") |
B17:B20 | B17 | =IFERROR(INDEX(B:B,MATCH(SMALL($C$2:$C$13,ROWS(B$17:B17)),$C$2:$C$13,0)),"") |
thanks. but unfortunately i am using version 2016 and 2019. and i guess .unique and filter formula are not available in these verson?This will do it
Excel Formula:=UNIQUE(FILTER(B:B,A:A=1,""))
any order,Do you want the results in table order, or sorted ascending / descending?
Also, what is in column B, text or numbers?
faizee.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Ref | List | Results | |||
2 | 3 | 1 | 1 | |||
3 | 1 | 1 | 2 | |||
4 | 2 | 4 | 3 | |||
5 | 1 | 4 | 4 | |||
6 | 3 | 3 | ||||
7 | 1 | 2 | ||||
8 | 1 | 2 | ||||
9 | 1 | 4 | ||||
10 | 2 | 2 | ||||
11 | 1 | 1 | ||||
12 | 2 | 4 | ||||
13 | 1 | 3 | ||||
14 | 3 | 1 | ||||
15 | 2 | 2 | ||||
16 | 2 | 1 | ||||
17 | 3 | 4 | ||||
18 | 3 | 3 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D6 | D2 | =IFERROR(AGGREGATE(15,6,$B$2:$B$18/($A$2:$A$18=1),1+SUM(COUNTIFS($A$2:$A$18,1,$B$2:$B$18,D$1:D1))),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |