Collate a Data's based on Ref No.

Are you OK t use VBA for this? If not, what is the MAXIMUM number of products that will be collated in one cell?

Here you go, I used UDF & INDEX+MATCH to consolidate as you required,
Pls refer the sheet for more info
UDF:

Please Login or Register  to view this content.

Non-VBA alternative.

Helper in D3, copied down:
=IFERROR(B3&", "&INDEX(D4:D16,MATCH(A3,A4:A16,0)),B3)

In H3, copied down:
=IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($H$2:$H2,$A$2:$A$16),0),0)),"")

In I3, copied down:
=IF(H3="","",INDEX($D$3:$D$16,MATCH(H3,$A$3:$A$16,)))

and in J3, copied down:
=IF(H3="","",SUMIF(A:A,H3,C:C))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.