Link to home
Start Free TrialLog in
Avatar of Patrick O'Dea
Patrick O'DeaFlag for Ireland

asked on

Excel - Transposing columns/rows (sort of!)

See attached.

I have a list of customers in column "A" and their bank account number in column "B".
When the customer has more than one bank account then I want the two accounts merged into a single cell - separated by a comma.

See the "Yellow" and "Orange ".
I require the "orange"

Note that I have 30,000 rows.



User generated imageEE_Transpose.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you need VBA to do your process?
If you can use VBA this should work for you:
Sub CombineAccounts()
Dim LastCust

LastCust = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
CountAcct = 1
StartOver:
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    If c.Value = Range("A" & c.Row - 1).Value Then
        Range("B" & c.Row).Value = Range("B" & c.Row - 1).Value & ", " & Range("B" & c.Row).Value
        Range("A" & c.Row - 1).Select
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        GoTo StartOver
    End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

You might like to try this macro

Sub xform()
Dim customerdata()
Dim cel As Range
Dim n As Long
Dim nrows As Long
nrows = Range("a2", Range("a2").End(xlDown)).Rows.Count
ReDim customerdata(nrows - 1, 1)
'customerdata = Range("a2", Range("a2").End(xlDown)).Resize(, 2).Offset(100)
For Each cel In Range("a2", Range("a2").End(xlDown))
customerdata(n, 0) = cel
customerdata(n, 1) = customerdata(n, 1) & IIf(customerdata(n, 1) = "", "", ",") & cel.Offset(, 1)
If cel.Offset(1) <> cel Then n = n + 1
Next cel
Range("a2", Range("a2").End(xlDown)).Resize(, 2) = customerdata
End Sub
Please find attached...
EE_Transpose_2.xlsm
Avatar of Patrick O'Dea

ASKER

Perfect ... nice simple elegant solution.
Just what I was trying to do!
Glad to help.