Move cells in columns according to the Value in Column A

[fddekker]

I have a spreadsheet that is more than 100's of thousands of rows. In smaller cases, I would simply insert subtotals and then copy and paste visible cells to another sheet to see all the items related to a specific account in one row, but in this case I can't do it. How can I use VBA to check if the amount in a column belongs to an account number in column A that have more than one entry. If the account number has more than one entry, the amount should be moved to the top row of that specific account. All amounts related to an account should thus be in the same row. I am adding a screenshot of the raw data (left) and the expected layout (right).

Any help appreciated.

 

[rpaulson]

try this on a copy of your file

Sub do_It()
Application.ScreenUpdating = False
acct = [A2]
sr = 2

For r = 3 To Range("A" & Rows.Count).End(xlUp).Row

If Cells(r, "A") = acct Then
    For c = 3 To 7
        If Cells(r, c) <> "" Then Cells(sr, c) = Cells(r, c)
        Cells(r, c) = ""
        Cells(r, "A") = ""
    Next c
    
Else
    acct = Cells(r, "A")
    sr = r
End If

Next r
Application.ScreenUpdating = True
End Sub

hth,
Ross

 

[fddekker]

Fantastic!!! Thank you soo much, and with such efficient code!