This proposal employs a helper column (C) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,COUNTIFS(A$2:A$14,A2,B$2:B$14,B2),"")
Cells F11:H14 are populated using: =IFERROR(INDEX($A$2:$A$14,AGGREGATE(15,6,(ROW($A$2:$A$14)-ROW($A$1))/($C$2:$C$14=AGGREGATE(14,6,$C$2:$C$14/($B$2:$B$14=F$10),ROWS($A$1:$A1)))/($B$2:$B$14=F$10),1)),"")
Cells F7:H7 are populated using: =F11
Let us know if you have any questions.
Bookmarks