Excel Macro - Combined the text in different cells to a new cell with many variables

Hi all excel macro experts,

I face difficulties in writing this macro. The situation is I have a raw excel data for some item and its remark. For example, column A is Item No., column B is Remarks, see picture below. How can I write this macro to combine the remarks to column C if they are the same item no. in column A? Please note that the combine text need to place on the first row of the different item no.

I try to write the below macro but it seems doesn't work because it didn't know how many rows item no. have (5377 have 5 rows, 5503 have 4 rows) in order to combine the correct results in column C. The result I want is at the below picture. Many thanks if any experts can help me!

Sub combine()

Dim a, b As Integer

a = 3
b = 2

Do

If Cells(b + 1, 1).Value <> Cells(b, 1).Value Then

Cells(b, 3).Value = Cells(b - a - 3, 2) & Cells(b - a - 2, 2) & Cells(b - a - 1, 2) & Cells(b - a, 2) & Cells(b, 2)

End If

b = b + 1

Loop Until Cells(b, 1).Value = ""

End Sub

macro.jpg

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

Thanks, it works well! Once again appreciate your effort!!