Try this...
Data Range
A B C D E 1 ------ Campus2 Campus5 2 Campus2 Sheerish Madipadaga Balachandar Manoharan Kalyan Gorti 3 Campus2 Ramesh Bandaru Nikunj Goel Prashant Kamal 4 Campus5 Kalyan Gorti Nisha Shine 5 Campus2 Balachandar Manoharan Ramesh Bandaru 6 Campus5 Prashant Kamal Sheerish Madipadaga 7 Campus2 Sridhar G Sridhar G 8 Campus2 Nisha Shine 9 Campus2 Nikunj Goel
This array formula** entered in D2:
=IFERROR(INDEX($B$2:$B$9,MATCH(SMALL(IF(FREQUENCY(IF($A$2:$A$9=D$1,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW(B$2:B$9)-ROW(B$2)+1),COUNTIF($B$2:$B$9,"<"&$B$2:$B$9)),ROWS(D$2:D2)),COUNTIF($B$2:$B$9,"<"&$B$2:$B$9),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy across to E2 then down until you get a row full of blanks.
Bookmarks