PC Review


Reply
Thread Tools Rate Thread

comparing data in different rows

 
 
Daniel
Guest
Posts: n/a
 
      7th Jul 2008
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
 
Reply With Quote
 
 
 
 
Max
Guest
Posts: n/a
 
      7th Jul 2008
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

 
Reply With Quote
 
BobT
Guest
Posts: n/a
 
      7th Jul 2008
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

 
Reply With Quote
 
Daniel
Guest
Posts: n/a
 
      7th Jul 2008
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,TRUE)

"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

 
Reply With Quote
 
Max
Guest
Posts: n/a
 
      8th Jul 2008
> =IF(ISNA(VLOOKUP(A1,$F$2:$F$52,1,FALSE)),FALSE,TRUE)

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
---
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing multiple rows of data stewpot Microsoft Excel Programming 7 14th Jul 2008 03:14 PM
Comparing several rows of data for 1 record stewpot Microsoft Excel Programming 2 4th Jul 2008 10:44 AM
Comparing data between sheets, and copying rows with data =?Utf-8?B?Rmxlb25l?= Microsoft Excel Programming 1 2nd Jun 2006 06:54 PM
Comparing two Data Rows =?Utf-8?B?VmFtc2kgUG9sYXZhcmFwdQ==?= Microsoft Dot NET 0 3rd May 2005 01:22 PM
Comparing rows of data matlocktm Microsoft Excel Misc 2 4th Aug 2004 11:42 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:10 AM.