Transpose automatically

mystique07

New Member
Joined
Mar 30, 2018
Messages
12
I have a list of names and account in different columns. i need to transpose all the account names to one name as mentioned in the below.
each name has different number of accounts to it.

NameAccount
Appleatlspmgr2atlspmgr2ATLPDBS1atlspint1aaipvmgraaipvmgratlspmgr
AppleATLPDBS1
Appleatlspint1
Appleaaipvmgr
Appleaaipvmgr
Appleatlspmgr
Bananasafepvmgrsafepvmgrsafeftp
Bananasafeftp
Grapesdcorrpsqldcorrpsqlsecrpsql
Grapessecrpsql

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
mystique07,

If you're comfortable with a vba approach, you might consider the following...

Code:
Sub TransposeAccounts_1066307()
Application.ScreenUpdating = False
Dim startRow As Long, endRow As Long, lastRow As Long, i As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
startRow = 2
endRow = 2
For i = 3 To lastRow
    If Cells(i, 1).Value = Cells(startRow, 1).Value Then
        endRow = i
    Else
        Range(Cells(startRow, 2), Cells(endRow, 2)).Copy
        ActiveSheet.Cells(startRow, 4).PasteSpecial Transpose:=True
        startRow = endRow + 1
        endRow = startRow
        Application.CutCopyMode = False
    End If
Next i
End Sub

It's assumed the Names are in Column A and Accounts in Column B, and that Names are sorted.

Cheers,

tonyyy
 

Watch MrExcel Video

Forum statistics

Threads
1,109,531
Messages
5,529,389
Members
409,870
Latest member
Well59
Top