Patrick O'Dea
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.
EE_Transpose.xlsx
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.
EE_Transpose.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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)).R ows.Count
ReDim customerdata(nrows - 1, 1)
'customerdata = Range("a2", Range("a2").End(xlDown)).R esize(, 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)).R esize(, 2) = customerdata
End Sub
Sub xform()
Dim customerdata()
Dim cel As Range
Dim n As Long
Dim nrows As Long
nrows = Range("a2", Range("a2").End(xlDown)).R
ReDim customerdata(nrows - 1, 1)
'customerdata = Range("a2", Range("a2").End(xlDown)).R
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)).R
End Sub
Please find attached...
EE_Transpose_2.xlsm
EE_Transpose_2.xlsm
ASKER
Perfect ... nice simple elegant solution.
Just what I was trying to do!
Just what I was trying to do!
Glad to help.