Excel Advance what/if/else statement

Posted on 2012-08-21
Last Modified: 2012-09-05
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.
Question by:DAN2011
    1 Comment
    LVL 39

    Accepted Solution

    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
        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)
    Columns("D:E").Copy Columns("B:C")
    Application.ScreenUpdating = True 'reenable ScreenUpdating
    End Sub

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now