Solved

# Excel - comparing columns

Posted on 2012-09-08
658 Views
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
0
Question by:tommym121

LVL 10

Expert Comment

Do all three fields have to match?
0

Author Comment

Yes,
0

LVL 43

Expert Comment

Without VBA you can try entering this formula in G1 and copying it across to I1 and down to the end of your data

=IF(ISERROR(MATCH(\$B1,OFFSET(\$D\$1,MATCH(\$A1,\$D:\$D,0)-1,1,COUNTIF(\$D:\$D,\$A1)),0)),"",INDEX(D:D,MATCH(\$B1,OFFSET(\$D\$1,MATCH(\$A1,\$D:\$D,0)-1,1,COUNTIF(\$D:\$D,\$A1)),0)+MATCH(\$A1,\$D:\$D,0)-1))
0

Author Comment

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

LVL 43

Expert Comment

Can you post a file showing how you want to see it?
0

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
End With
End If
End With
Next i
End Sub
``````
0

Author Closing Comment

Thanks.
0

## Featured Post

### 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.