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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Customer id[/td][td=bgcolor:#5B9BD5]account number[/td][td][/td][td=bgcolor:#70AD47]Customer id[/td][td=bgcolor:#70AD47]account number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cust1[/td][td=bgcolor:#DDEBF7]acc01[/td][td][/td][td=bgcolor:#E2EFDA]Cust1[/td][td=bgcolor:#E2EFDA]acc01[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cust2[/td][td]acc02[/td][td][/td][td]Cust2[/td][td]acc02[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cust3[/td][td=bgcolor:#DDEBF7]acc03[/td][td][/td][td=bgcolor:#E2EFDA]Cust3[/td][td=bgcolor:#E2EFDA]acc03&acc04&acc05[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cust3[/td][td]acc04[/td][td][/td][td]Cust500[/td][td]acc1999&acc2000[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cust3[/td][td=bgcolor:#DDEBF7]acc05[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cust500[/td][td]acc1999[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cust500[/td][td=bgcolor:#DDEBF7]acc2000[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]