How to merge all the cell content with conditions

[Mike Chester]

Hi all,

It's my first time to post here and although I'm not a newbie anymore in Excel, I still most of the times do need the help of an expert.

I have a spreadheet here whereas, on the 1st column (Col A) the "Reference" are listed on number of rows with corresponding Tag Number (Col B), Sub-Equipment Nos. 1 and 2 (col C and D).

In Sheet 2, I want to create a report such that I will only take the reference number once in Col A, and all the information from col B, C & D will be concatenated in Col B of sheet 2.

I need to know what function formula I can apply in Col A of sheet 2 to get only one reference out of the group of reference number in sheet 1. Also a formula to concatenate all the information of this reference in Col B.

Attached is a sample spreadsheet for easy reference. Thanks.