comparing data in different rows
 

i'm working on a long (approx 5000 lines) data sheet and i have a different
list containing erroneous values that relate to a particular row in the 1st
data sheet. i would need to run a function which gives me say a true/false
next to each cell in this particular row wherever it finds a matching value
from the second list of errors. more specifically, say i have the value '1'
in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
the error list, therefore i would like to see TRUE next to this cell, and say
i have '5' in the cell then i would like to see FALSE in the cell next to it
(since '5' is not listed as an erroneous value in the 2nd list). well, hope
this makes sense. i have been trying to create a multi-function for this
using IF, VLOOKUP etc. without success and it's extremely time consuming to
delete each erroneous value manually. any help is appreciated

comparing data in different rows
 

One guess ..
Assuming source data in A2 down, place this in B2:
=IF(A2="","",ISNUMBER(MATCH(A2,{1;2;3;4},0)))
Copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Daniel" wrote:
i'm working on a long (approx 5000 lines) data sheet and i have a different
list containing erroneous values that relate to a particular row in the 1st
data sheet. i would need to run a function which gives me say a true/false
next to each cell in this particular row wherever it finds a matching value
from the second list of errors. more specifically, say i have the value '1'
in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
the error list, therefore i would like to see TRUE next to this cell, and say
i have '5' in the cell then i would like to see FALSE in the cell next to it
(since '5' is not listed as an erroneous value in the 2nd list). well, hope
this makes sense. i have been trying to create a multi-function for this
using IF, VLOOKUP etc. without success and it's extremely time consuming to
delete each erroneous value manually. any help is appreciated

comparing data in different rows
 

Use this:

=IF(ISNA(VLOOKUP(Your Lookup Value,The range of error values - 1 to 4 in
your example,1,FALSE)),FALSE,TRUE)

Basically, if the Vlookup doesn't find your value in the range of error
values (e.g. it won't find 5 in your 1-4 example), then the ISNA is true
(i.e. Vlookup returns a #N/A) and so the IF statement returns a FALSE,
otherwise, we found a match and it returns TRUE.

"Daniel" wrote:

i'm working on a long (approx 5000 lines) data sheet and i have a different
list containing erroneous values that relate to a particular row in the 1st
data sheet. i would need to run a function which gives me say a true/false
next to each cell in this particular row wherever it finds a matching value
from the second list of errors. more specifically, say i have the value '1'
in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
the error list, therefore i would like to see TRUE next to this cell, and say
i have '5' in the cell then i would like to see FALSE in the cell next to it
(since '5' is not listed as an erroneous value in the 2nd list). well, hope
this makes sense. i have been trying to create a multi-function for this
using IF, VLOOKUP etc. without success and it's extremely time consuming to
delete each erroneous value manually. any help is appreciated

comparing data in different rows
 

Wow BobT!! thank you, you saved me about half a days work! :) it did the work
perfectly. Many thanks
fyi, here's what i ended up using
=IF(ISNA(VLOOKUP(A1,$F$2:$F$52,1,FALSE)),FALSE,TRU E)

"BobT" wrote:

Use this:

=IF(ISNA(VLOOKUP(Your Lookup Value,The range of error values - 1 to 4 in
your example,1,FALSE)),FALSE,TRUE)

Basically, if the Vlookup doesn't find your value in the range of error
values (e.g. it won't find 5 in your 1-4 example), then the ISNA is true
(i.e. Vlookup returns a #N/A) and so the IF statement returns a FALSE,
otherwise, we found a match and it returns TRUE.

"Daniel" wrote:

i'm working on a long (approx 5000 lines) data sheet and i have a different
list containing erroneous values that relate to a particular row in the 1st
data sheet. i would need to run a function which gives me say a true/false
next to each cell in this particular row wherever it finds a matching value
from the second list of errors. more specifically, say i have the value '1'
in the first data sheet and i have '1', '2', '3', '4' as erroneuos values in
the error list, therefore i would like to see TRUE next to this cell, and say
i have '5' in the cell then i would like to see FALSE in the cell next to it
(since '5' is not listed as an erroneous value in the 2nd list). well, hope
this makes sense. i have been trying to create a multi-function for this
using IF, VLOOKUP etc. without success and it's extremely time consuming to
delete each erroneous value manually. any help is appreciated

comparing data in different rows
 

=IF(ISNA(VLOOKUP(A1,$F$2:$F$52,1,FALSE)),FALSE,TRU E)

If it's just a single col check ..
this adaptation of my earlier suggestion should work as well:
=IF(A1="","",ISNUMBER(MATCH(A1,$F$2:$F$52,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---