Pulling data with duplicate rankings

Is "David, James" to appear in one cell or would you be able to list the names in adjacent columns ?

As a rule of thumb, Excel is not good at working with String Arrays in native functions and invariably if the concatenation approach is your requirement then I'm afraid often you're looking at VBA or using 3rd party utilities like morefunc.xll (specifically MCONCAT function).

There is however the possibility of using helper cells to alleviate the strain but we'd need to know more to be sure...

In basic terms --- regards Table 1, assuming columns A:B in use with header row in row 1, then you could add an additional column of data @ source:

C2: =LOOKUP(2,1/($B$1:$B1=$B2),$C$1:$C1)&", "&$A2
copied down for all rows

Then assuming Table2 is say in Columns E:F with headers in row 1

E2: =IF(ROWS(E$2:E2)>MAX($B:$B),"",ROWS(E$2:E2))
copied down as required

F2: =IF($E2="","",LOOKUP(2,1/($B$2:$B$100=$E2),$C$2:$C$100)

the above may not work depending on volume of names etc...and might require a slight change of tack.

If you can provide a sample file which illustrates your data setup and desired output that would be helpful.

As suggested, post a sample and outline desired results - at present we're guessing at your set-up etc and what works in one scenario may not work in another.

See if the attached is along the lines of your requirements - the formatting is merely to illustrate those cells I've added/amended.