sg,
Copy your data to a new sheet and run this
Cecil

Option Explicit

Sub OrganizeData()
Dim LRow As Double
Dim i As Double

LRow = Range("B" & Rows.Count).End(xlUp).Row

Range("A1:D" & LRow).Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

For i = LRow To 2 Step -1
If Range("B" & i).Value = Range("B" & i - 1).Value Then
Range("D" & i - 1).Value = _
Range("D" & i - 1).Value & "," & Range("D" & i).Value
Range("B" & i).EntireRow.Delete
End If
Next i

End Sub

"sg" wrote in message
...
Hi, I'm assigned an task that to organize one file like this:

original table:
sn# ref# cir# ID#
4232 23 OT111000 F1000
4545 23 OT111000 F1001
8686 45 OT111000 F1002
8989 46 OT118989 F4230
7899 46 OT118989 F4231


the table wanted:
If Ref# and cir# are same, concatenate ID# together, like sample below:
sn# ref# cir# ID#
(any) 23 OT111000 F1000, F1001
8686 45 OT111000 F1002
(any) 46 OT118989 F4230, F4231

I like to run a macro to complete this because the table has tons of
records. Can anyone help me on this?

Any help would be greatly appreciated.

Sarah