I have a spreadsheet which lists a number of people in column A.

Example

Brian
Martin
John
Joe Bloggs
Richard

In another spreadsheet, I have a list of people and also accounts in columns A and B.

Example

Brian - Account1
Brian - Account2
Brian - Account3
Brian - Account4
Martin - Account1
Martin - Account2
John - Account1
John - Account2

Is there anyway I can pull this data through into a singular cell in my main workbook seperated by a comma.

So in otherwards one of my cells on the row representing Brian shows as

Account1, Account2, Account3, Account4,

and the row representing Martin shows as

Account1, Account2,

I know a pivot table would probably do the job, however, this spreadsheet is pretty much all macro driven so was hoping to build this code into the macro?

Thanks in advance