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

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

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
Asked:
tommym121
  • 3
  • 2
  • 2
1 Solution
 
tdlewisCommented:
Do all three fields have to match?
0
 
tommym121Author Commented:
Yes,
0
 
Saqib Husain, SyedEngineerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tommym121Author Commented:
not what I expected,  I am trying to make it more visual so I can see the changes easier
0
 
Saqib Husain, SyedEngineerCommented:
Can you post a file showing how you want to see it?
0
 
tdlewisCommented:
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

0
 
tommym121Author Commented:
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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