Compare Before Insert Excel

Posted on 2012-09-18
Last Modified: 2012-09-18
Hello All,

I have a compare / insert issue before copy paste. Basically, data from SourceSRC is getting copy-pasted over to MainSRC. Now SourceSRC usually has lesser columns. Instead of manually inserting blank columns into the SourceSRC table, can a VBA do that, to make sure the data getting copy pasted is happening between identical column count tables…now the mainSRC is the standard table, so the SourceSRC has to have the blank columns inserted to match with the standard table before getting copied – pasted over

VBA pseudo
Go Through each column header cells in mainSRC
      If a column header column is missing in SourceSRC (that is present in mainSRC) – then insert that a brand new column in SourceSRC with the column header name
Loop till the last header cell in mainSRC

Thank you
Question by:Rayne
    LVL 39

    Accepted Solution

    Here's the code. It won't copy the data yet, but it will insert and move columns if required.

    Sub MatchColumns()
    'Matches two ranges by inserting columns in both ranges so each value is on the same column
    'you first need to
    '   select the two data blocks you want to split and match
    Dim rg1 As Range, rg2 As Range
    Dim firstMatch As Boolean
    Dim i As Long, j As Long, foundCol As Long
    Application.ScreenUpdating = False
        Set rg1 = Sheets("mainSRC").[b4].CurrentRegion
        Set rg2 = Sheets("sourceSRC").[b7].CurrentRegion
        'gets the number of unique values in the first rows of range 1 and 2, to be able to run the loop all the way
        Dim cUnique As New Collection
        On Error Resume Next
        With rg1
            For i = 1 To .Rows(1).Cells.Count
                cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
        End With
        With rg2
            For i = 1 To .Rows(1).Cells.Count
                cUnique.Add .Cells(1, i), CStr(.Cells(1, i))
        End With
        On Error GoTo 0
        'boolean needed to be able to resize range 2 if required
        firstMatch = True
        For i = 1 To cUnique.Count
            If Len(rg1.Cells(1, i)) = 0 Or rg2.Cells(1, i) = rg1.Cells(1, i) Then
                firstMatch = False
                GoTo nxt_i:
            End If
            On Error Resume Next
            foundCol = rg2.Rows(1).Find(What:=rg1.Cells(1, i), LookIn:= _
                xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False).Column
            If Err <> 0 Then
                rg2.Offset(, i - 1).Resize(, 1).Insert Shift:=xlToRight
                If firstMatch Then Set rg2 = rg2.Offset(, -1).Resize(, rg2.Columns.Count + 1)
                rg2.Columns(foundCol - rg2.Column + 1).Cut
                rg2.Columns(i + rg2.Column - 1).Insert Shift:=xlToRight
                firstMatch = False
            End If
    Application.ScreenUpdating = True
    End Sub

    Open in new window


    Author Comment

    Perfection to the Ultimate,

    Thank you Thomas,
    LVL 39

    Expert Comment

    Glad I had something in my macro toolbox for you. Thanks for the kind words.


    Author Comment

    You are welcome :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    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,…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 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

    13 Experts available now in Live!

    Get 1:1 Help Now