Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

Then use these formula
Note: "InvNo" and "SerialNo" are defined name ranges

To Get the unique record:

In D2:
=IF(ISERR(SMALL(IF(MATCH(InvNo,InvNo,0)=ROW(INDIRE CT("1:"&ROWS(InvNo))),MATCH(InvNo,InvNo,0)),ROWS($ 1:1))),"",INDEX(InvNo,SMALL(IF(MATCH(InvNo,InvNo,0 )=ROW(INDIRECT("1:"&ROWS(InvNo))),MATCH(InvNo,InvN o,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

In E2: =SUBSTITUTE(TRIM(MCONCAT(IF(InvNo=$D2,SerialNo,"") &" "))," ",",")

ctrl+shift+enter, not just enter
copy down




"Michael D" wrote:

I have the following data:

A B
Inv No. Serial No.
0001 ABC
0001 DEF
0001 GHI
0002 JKL
0002 MNO
0002 PQR

and need to come back to this:

Inv No. Serial No
0001 ABC, DEF, GHI
0002 JKL, MNO, PQR


Would anyone be able to help me out