Excel Advance what/if/else statement

How do I create a statement that will give me the desire results displayed in the example attached. Currently in my spreadsheet I have the same  number (accounttop) for the same number and same bottom_account_2. How do I get it to display on one line, where top_account goes into top_account and top_account_1, this of course will only happen when  the number in colunm A and  G is duplicated.
Bookexp.xlsx
DAN2011Asked:
Who is Participating?
 
nutschCommented:
This should do it, assuming you start your data in row 1, and assuming that you only want to consolidate the rows where the column A numbers match.

Sub ConsolidateRows()
'takes rows and consolidate one or many cells, based on one or many cells matching with above or below rows.

Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant

'**********PARAMETERS TO UPDATE****************
Const strMatch As String = "A"    'columns that need to match for consolidation, separated by commas
Const strConcat As String = "D,G"     'columns that need consolidating, separated by commas
Const strSep As String = "; "     'string that will separate the consolidated values
'*************END PARAMETERS*******************

Application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes

colMatch = Split(strMatch, ",")
colConcat = Split(strConcat, ",")

lastRow = Range("A" & Rows.Count).End(xlUp).Row 'get last row

For i = lastRow To 2 Step -1 'loop from last Row to one
    
    For j = 0 To UBound(colMatch)
        If Cells(i, colMatch(j)) <> Cells(i - 1, colMatch(j)) Then GoTo nxti
    Next
    
    For j = 0 To UBound(colConcat)
        If Cells(i - 1, colConcat(j)) <> Cells(i, colConcat(j)) Then Cells(i - 1, colConcat(j)).Copy Cells(i - 1, colConcat(j)).Offset(, 1)
    Next
    
    Rows(i).Delete
    
nxti:
Next

Columns("D:E").Copy Columns("B:C")
Columns("D:E").ClearContents

Application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.