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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 687

# Excel - comparing columns

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
tommym121
• 3
• 2
• 2
1 Solution

Commented:
Do all three fields have to match?
0

Author Commented:
Yes,
0

EngineerCommented:
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 Commented:
not what I expected,  I am trying to make it more visual so I can see the changes easier
0

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

Commented:
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 Commented:
Thanks.
0

## Featured Post

• 3
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.