|
A |
B |
C |
D |
E |
1 |
CODE |
DRP |
|
COUNT |
CODE |
2 |
Apple Inc (XNAS:AAPL) |
no |
|
1 |
Rio Tinto Ltd (XASX:RIO) |
3 |
Microsoft Corp (XNAS:MSFT) |
No |
|
|
|
4 |
Tesla Inc (XNAS:TSLA) |
No |
|
|
|
5 |
Rio Tinto Ltd (XASX:RIO) |
Yes |
|
|
|
6 |
Cochlear Ltd (XASX:COH) |
No |
|
|
|
7 |
CSL Ltd (XASX:CSL) |
No |
|
|
|
8 |
Apple Inc (XNAS:AAPL) |
No |
|
|
|
9 |
Tesla Inc (XNAS:TSLA) |
No |
|
|
|
10 |
Rio Tinto Ltd (XASX:RIO) |
Yes |
|
|
|
D2=SUM(IF(FREQUENCY(IF($B$2:$B$100="Yes",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))
Control+shift+enter
E2=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF($B$2:$B$100="yes",MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:A2))),"")
Control+shift+enter
copy down
Bookmarks