Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF/SUMIF comparing two rows of data | Excel Worksheet Functions | |||
Macro Help Needed: Comparing cell values and deleting rows | Excel Discussion (Misc queries) | |||
Formula for comparing rows in a column | Excel Discussion (Misc queries) | |||
comparing two rows, then highting? | Links and Linking in Excel | |||
Comparing two cells in a colum with 2000 rows | Excel Discussion (Misc queries) |