Compare 2 colmns based off data in other columns
 

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 2 colmns based off data in other columns
 

.. compare the deduction codes from both lists to
see which employees had the deduction come out.

Based on your sample data posted, take us through an example or 2 of how the
above is done
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Compare 2 colmns based off data in other columns
 

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

"Max" wrote:

.. compare the deduction codes from both lists to
see which employees had the deduction come out.

Based on your sample data posted, take us through an example or 2 of how the
above is done
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Compare 2 colmns based off data in other columns
 

Try this in E2:
=IF(B2<"MED","",IF(AND(B2="MED",INDEX(D:D,MATCH(A 2,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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" wrote:
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

Compare 2 colmns based off data in other columns
 

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.

"Max" wrote:

Try this in E2:
=IF(B2<"MED","",IF(AND(B2="MED",INDEX(D:D,MATCH(A 2,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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" wrote:
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

Compare 2 colmns based off data in other columns
 

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=B 2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" wrote:
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.

Compare 2 colmns based off data in other columns
 

Perfect. Much Thnx!

"Max" wrote:

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=B 2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" wrote:
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.

Compare 2 colmns based off data in other columns
 

welcome, Steve.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" wrote in message
...
Perfect. Much Thnx!