Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and copy rows of data based on an ID# | Excel Discussion (Misc queries) | |||
compare data in two columns | Excel Worksheet Functions | |||
Compare data in two columns | Excel Worksheet Functions | |||
Need to compare data in 2 columns and not sure how. | Charts and Charting in Excel | |||
Trying to compare data in two columns... | Excel Worksheet Functions |