asked on

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

tdlewis

Do all three fields have to match?

tommym121

ASKER

Yes,

Saqib Husain

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))

tommym121

ASKER

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

Saqib Husain

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

ASKER CERTIFIED SOLUTION

tdlewis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tommym121

ASKER

Thanks.