Excel - comparing columns

Posted on 2012-09-08
Last Modified: 2012-09-08
I am trying to compare two set of columns (A,B,C and D, E, F). I need to shift the 2nd set of data (D,E,F)  down to match the first set (A,B,C).  I will like to change the fill color of the shift space.  I have to repeatedly do  this till I reach the end of my 2nd set.

Can anyone show me what I need to do.  The purpose is to identify the difference between two set. All the data is in order with respect to ID

A      B             C              D      E             F
ID      Name      Status      ID      Name      Status
1      Mark      0               1      John              1
1      John              1               2      Mary      1
2      Mary      1               3      Jan           1
3      Jan              1             3      Gen          0      
3      Steve      0                  
3      Gen              0
This is the result                              
ID      Name      Status      ID      Name      Status
1      Mark             0             
1      John              1             1      John                1      
2      Mary      1               2      Mary        1
3      Jan              1               3      Jan                1
3      Steve      0                  
3      Gen          0              3      Gen          0
Question by:tommym121
    LVL 10

    Expert Comment

    Do all three fields have to match?

    Author Comment

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Without VBA you can try entering this formula in G1 and copying it across to I1 and down to the end of your data


    Author Comment

    not what I expected,  I am trying to make it more visual so I can see the changes easier
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Can you post a file showing how you want to see it?
    LVL 10

    Accepted Solution

    Try the following VBA routine:
    Sub CompareColumns()
    Dim i As Long
    Dim lastA As Long
    Dim lastD As Long
    Dim lastRow As Long
        lastA = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
        lastD = ActiveSheet.Range("D" & ActiveSheet.Rows.Count).End(xlUp).Row
        lastRow = IIf(lastA > lastD, lastA, lastD)
        For i = 2 To lastRow
            With ActiveSheet.Range("A" & i)
                If .Cells(1, 1).Value <> .Cells(1, 4).Value _
                  Or .Cells(1, 2).Value <> .Cells(1, 5).Value _
                  Or .Cells(1, 3).Value <> .Cells(1, 6).Value Then
                    ActiveSheet.Range("D" & i & ":F" & i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    With ActiveSheet.Range("D" & i & ":F" & i).Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                End If
            End With
        Next i
    End Sub

    Open in new window


    Author Closing Comment


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    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.

    754 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

    18 Experts available now in Live!

    Get 1:1 Help Now