Reduce rows for same customer id

[Kwanjau]

I have two columns, on the first 1000 customer numbers and in the second an account number.
About 500 customers have multiple account numbers.
I want to reduce the lines.
I want only one line per customer number and in column two a string of the corresponding account numbers delimited by the & symbol.

Is there a multiple step way or do i require a macro?

 

[Michael M]

can you post a small sample ??

 

[Kwanjau]

An example
Customer id - account number
Cust1 - acc01
Cust2 - acc02
Cust3 - acc03
Cust3 - acc04
Cust3 - acc05
.
.
Cust500 - acc1999
Cust500 - acc2000

Aim:
Cust1 - acc01
Cust2 - acc02
Cust3 - acc03&acc04&acc05
.
.
Cust500 - acc1999&acc2000

 

[Michael M]

Maybe this, assuming Columns "A" & "B"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Cells(r, 1).Value = Cells(r - 1, 1).Value Then
        Cells(r - 1, 2).Value = Cells(r - 1, 2).Value & "&" & Cells(r, 2).Value
        Rows(r).Delete
    End If
Next r
End Sub

 

[Kwanjau]

Maybe this, assuming Columns "A" & "B"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Cells(r, 1).Value = Cells(r - 1, 1).Value Then
        Cells(r - 1, 2).Value = Cells(r - 1, 2).Value & "&" & Cells(r, 2).Value
        Rows(r).Delete
    End If
Next r
End Sub

Thank you, I'll try it out.

 

[sandy666]

also you can try PowerQuery aka Get&Transform

Customer id	account number		Customer id	account number
Cust1	acc01		Cust1	acc01
Cust2	acc02		Cust2	acc02
Cust3	acc03		Cust3	acc03&acc04&acc05
Cust3	acc04		Cust500	acc1999&acc2000
Cust3	acc05
Cust500	acc1999
Cust500	acc2000

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Group = Table.Group(Source, {"Customer id"}, {{"Count", each _, type table}}),
    TblList = Table.AddColumn(Group, "account number", each Table.Column([Count],"account number")),
    Extract = Table.TransformColumns(TblList, {"account number", each Text.Combine(List.Transform(_, Text.From), "&"), type text})
in
    Extract[/SIZE]