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.

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

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

Try this in E2:
=IF(B2<>"MED","",IF(AND(B2="MED",INDEX(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.
--
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

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,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.
> --
> 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
>

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

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=B2),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.
>

welcome, Steve.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve C" <(E-Mail Removed)> wrote in message
news:1AA617A6-6B1F-4AA8-949C-(E-Mail Removed)...
> Perfect. Much Thnx!