[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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
0
DAN2011
Asked:
DAN2011
1 Solution
 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now