Row value concatination for matching rows of another column

[gunjanbadhey]

Hi All,
I am a new user of this forum. I am having problem in concatinating the data.
Like i Have excel and it contains two columns
FILE and screen.

which contain values like

File Screen
US1 INV1
US2 INV1
US3 LU1
US3 LU2
US1 A1
US1 A2
US3 A3
US2 D1
US2 R1
US2 R2
US1 R3
US3 A4
US2 A5
US1 AC

Now I want output as one column contain the unique values from cell 1 and another column cell contain concatenated values of cell 2 like

US1 INV1, A1,A2, R3, AC
US2 INV1, D1, R1, R2, A5
US3 LU1, LU2, A3, A4

How to do this in excel 2003?
Please Help

-Gunjan

[JBeaucaire]

Add the User Defined Function StringConcat located on this page (link) into your sheet. The code is at the bottom and should be placed in a Module.

http://www.cpearson.com/excel/stringconcatenation.aspx

Then just put US1, US2 ad US3 into 3 cells and enter the following array formula

=StringConcat(", ",IF($A$2:$A$15=C2,$B$2:$B$15,"")) and copy down. That formula is an array and must be confirmed with CTRL-SHIFT-ENTER, not just ENTER.

A workbook sample is attached.