I have got a issue and need some help. I have a sheet as follows:

Sector BR CC TCH_List
1 1 1113
1 2 329
1 3 1153
1 4 1155
1 5 1157
2 1 823
2 2 115
2 3 9
3 1 1145
3 2 817
3 3 789
3 4 1013
3 5 797

Finally, I want have a sheet like this:

Sector BR CC TCH_List
1 1 1113 1113;329;1153;1155;1157
2 1 823 823;115;9;1145
3 1 1145 1145;817;789;1013;797

I need all the CC column with the same sector value go to the TCH_list and
separate with ";". and just BR=1 rows are kept.

my question is: is there any macro or formula could be used for such a
request?

thank u.

Try This for combining second column values into the first row (in Col C) for
each distinct value in Col A

Sub CombineRows()
Dim Colb As String
Colb = ""
i = Range("A50").End(xlUp).Row
Do
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Colb = Cells(i, 2).Value & ";" & Colb

'Rows(i).Delete
Else
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""
End If
i = i - 1
Loop Until i < 2
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""

End Sub

Remove the comment if you want to delete the other rows. Increase 50 to
number of rows you have in i = Range("A50").End(xlUp).Row

You can adapt this to combine column C too...

Let me know if you need help with that...

"Coolquicc" wrote:

I have got a issue and need some help. I have a sheet as follows:

Sector BR CC TCH_List
1 1 1113
1 2 329
1 3 1153
1 4 1155
1 5 1157
2 1 823
2 2 115
2 3 9
3 1 1145
3 2 817
3 3 789
3 4 1013
3 5 797

Finally, I want have a sheet like this:

Sector BR CC TCH_List
1 1 1113 1113;329;1153;1155;1157
2 1 823 823;115;9;1145
3 1 1145 1145;817;789;1013;797

I need all the CC column with the same sector value go to the TCH_list and
separate with ";". and just BR=1 rows are kept.

my question is: is there any macro or formula could be used for such a
request?

thank u.

Thank you very much Sheeloo, which helped me a lot.

Just one thing I want to mention, a definition of "i" should be added before
we use it.

"Sheeloo" wrote:

Try This for combining second column values into the first row (in Col C) for
each distinct value in Col A

Sub CombineRows()
Dim Colb As String
Colb = ""
i = Range("A50").End(xlUp).Row
Do
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Colb = Cells(i, 2).Value & ";" & Colb

'Rows(i).Delete
Else
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""
End If
i = i - 1
Loop Until i < 2
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""

End Sub

Remove the comment if you want to delete the other rows. Increase 50 to
number of rows you have in i = Range("A50").End(xlUp).Row

You can adapt this to combine column C too...

Let me know if you need help with that...

"Coolquicc" wrote:

I have got a issue and need some help. I have a sheet as follows:

Sector BR CC TCH_List
1 1 1113
1 2 329
1 3 1153
1 4 1155
1 5 1157
2 1 823
2 2 115
2 3 9
3 1 1145
3 2 817
3 3 789
3 4 1013
3 5 797

Finally, I want have a sheet like this:

Sector BR CC TCH_List
1 1 1113 1113;329;1153;1155;1157
2 1 823 823;115;9;1145
3 1 1145 1145;817;789;1013;797

I need all the CC column with the same sector value go to the TCH_list and
separate with ";". and just BR=1 rows are kept.

my question is: is there any macro or formula could be used for such a
request?

thank u.

Glad I could help...

Declaration is necessary if you have OPTION EXPLICIT set... Good habit though

"Coolquicc" wrote:

Thank you very much Sheeloo, which helped me a lot.

Just one thing I want to mention, a definition of "i" should be added before
we use it.

"Sheeloo" wrote:

Try This for combining second column values into the first row (in Col C) for
each distinct value in Col A

Sub CombineRows()
Dim Colb As String
Colb = ""
i = Range("A50").End(xlUp).Row
Do
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Colb = Cells(i, 2).Value & ";" & Colb

'Rows(i).Delete
Else
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""
End If
i = i - 1
Loop Until i < 2
Colb = Cells(i, 2).Value & ";" & Colb
Cells(i, 4).Value = Colb
Colb = ""

End Sub

Remove the comment if you want to delete the other rows. Increase 50 to
number of rows you have in i = Range("A50").End(xlUp).Row

You can adapt this to combine column C too...

Let me know if you need help with that...

"Coolquicc" wrote:

I have got a issue and need some help. I have a sheet as follows:

Sector BR CC TCH_List
1 1 1113
1 2 329
1 3 1153
1 4 1155
1 5 1157
2 1 823
2 2 115
2 3 9
3 1 1145
3 2 817
3 3 789
3 4 1013
3 5 797

Finally, I want have a sheet like this:

Sector BR CC TCH_List
1 1 1113 1113;329;1153;1155;1157
2 1 823 823;115;9;1145
3 1 1145 1145;817;789;1013;797

I need all the CC column with the same sector value go to the TCH_list and
separate with ";". and just BR=1 rows are kept.

my question is: is there any macro or formula could be used for such a
request?

thank u.