Compare 2 colmns based off data in other columns

  • Thread starter Thread starter Steve C
  • Start date Start date
S

Steve C

i have a list of deduction codes from 2 different systems on the same
spreadsheet. i'm trying to compare the deduction codes from both lists to
see which employees had the deduction come out. i know how to use
=isna(match( to compare the deduction codes, but how can i compare the
deduction codes based off of employee number?

A B C D
EE #1 Ded Code1 EE #2 Ded Code2
111 MED 111 MED
222 MED 333 DEN
111 DEN 222 RET
333 RET 333 RET
 
.. compare the deduction codes from both lists to
see which employees had the deduction come out.
Click to expand...

Based on your sample data posted, take us through an example or 2 of how the
above is done

---
 
I tried this formula but it doesn't work. Row 3 should return True because
EE#222 doesn't have the med ded code on side 2

=IF(a2=c2:c5,ISNA(MATCH(b2,$d$2:$d$5,false)))

Side 1 Side 2
A B C D
EE #1 Ded Code1 EE #2 Ded Code2
111 MED 111 MED
222 MED 333 DEN
111 DEN 222 RET
333 RET 333 RET
 
Try this in E2:
=IF(B2<>"MED","",IF(AND(B2="MED",INDEX(D:D,MATCH(A2,C:C,0))="MED"),"Med
Deduction Ok","Check"))
Copy E2 down to the last row of data in cols A and B. Lines flagged "Check"
will be where the employee had a "MED" in col B w/o a corresponding "MED" in
col D.
 
This is the right idea, but it doesn't work because the deduction code
changes when i copy it down. There are thousands of Employees and over 100
different deduction codes.
 
Ok, think I see the intent better now. This should work for you.

In E2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,($C$2:$C$1000=A2)*($D$2:$D$1000=B2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.
 
Perfect. Much Thnx!

Max said:
Ok, think I see the intent better now. This should work for you.

In E2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,($C$2:$C$1000=A2)*($D$2:$D$1000=B2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.
Click to expand...
 
Back
Top