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

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

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

Thank you Cecil for your quick response, I definately will give a try. Sarah
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

Hi Cecil,

Thanks for your help, it just works perfectly for me.
Sarah
"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