Pulling all results through into one cell?

[mccrimmon]

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

[JBeaucaire]

That's called string concatenation, and it's not inherent to Excel. You'll have to add it in.

It's explained here:
http://www.cpearson.com/excel/stringconcatenation.aspx

Here's a sample sheet I have posted with it working.
http://www.excelforum.com/attachment...ringconcat.xls

After installing that UDF into your sheet, with your list above on Sheet2 and the name Brian in cell Sheet1!D1, the array formula would be:

=StringConcat(", ", IF(Sheet2!$A$1:$A$8=Sheet1!D1,Sheet2!$B$1:$B$8, "") )

Be sure to press CTRL-SHIFT-ENTER to confirm that formula, or it won't work.