Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract "Does not equal" data
Good morning
Have searched this and cant quite figure it out. I have a column containing 1122 numbers. They are unique numbers. I have another column that contains 359 of those 1122 numbers. How can I determine which are the remaining 763 numbers. In other words, not equal to the 359 numbers. Auto filter wont work; I cant copy in the 359 numbers after using the Does Not Equal filter in the Custom box from the Drop-down Autofilter menu. I am stumped! Thanks to anyone who takes the time to answer. Ill keep a close eye on this post for responses. sam |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract "Does not equal" data
Your 1122 are in a1:A1122
The 359 are in D1:D359 In B1 enter =IF(ISNA(LOOKUP(A1,$D$1:$D$359)),"X","") Copy down column All number that are not in the short list get an X in B Use Edit | Paste Special - Value on B Sort A: B using B as the sort key Of filter on B best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sam" wrote in message ... Good morning - Have searched this and can't quite figure it out. I have a column containing 1122 numbers. They are unique numbers. I have another column that contains 359 of those 1122 numbers. How can I determine which are the remaining 763 numbers. In other words, " not equal to" the 359 numbers. Auto filter won't work; I can't copy in the 359 numbers after using the "Does Not Equal" filter in the Custom box from the Drop-down Autofilter menu. I am stumped! Thanks to anyone who takes the time to answer. I'll keep a close eye on this post for responses. sam |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract "Does not equal" data
Oops, I was to quick for my own good!
Use =IF(ISNA(VLOOKUP(A1,$D$1:$D$9,1,FALSE)),"X","") -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "sam" wrote in message ... Good morning - Have searched this and can't quite figure it out. I have a column containing 1122 numbers. They are unique numbers. I have another column that contains 359 of those 1122 numbers. How can I determine which are the remaining 763 numbers. In other words, " not equal to" the 359 numbers. Auto filter won't work; I can't copy in the 359 numbers after using the "Does Not Equal" filter in the Custom box from the Drop-down Autofilter menu. I am stumped! Thanks to anyone who takes the time to answer. I'll keep a close eye on this post for responses. sam |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract "Does not equal" data
OR......copy the 359 and paste them below 1122...keep a row's gap so that
you know where they start from because you will delete them later. now select the column go to FilterAdvance Filtercheck COPY TO ANOTHER LOCATION box and check UNIQUE RECORDS ONLY box. choose your range. Once you have the unique ones...delete the ones you pasted in the first step. does it help? "sam" wrote in message ... Good morning - Have searched this and can't quite figure it out. I have a column containing 1122 numbers. They are unique numbers. I have another column that contains 359 of those 1122 numbers. How can I determine which are the remaining 763 numbers. In other words, " not equal to" the 359 numbers. Auto filter won't work; I can't copy in the 359 numbers after using the "Does Not Equal" filter in the Custom box from the Drop-down Autofilter menu. I am stumped! Thanks to anyone who takes the time to answer. I'll keep a close eye on this post for responses. sam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract "Does not equal" data
On Mar 31, 12:42*pm, "Bernard Liengme"
wrote: Oops, I was to quick for my own good! Use =IF(ISNA(VLOOKUP(A1,$D$1:$D$9,1,FALSE)),"X","") -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "sam" wrote in message ... Good morning - Have searched this and can't quite figure it out. I have a column containing 1122 numbers. They are unique numbers. I have another column that contains 359 of those 1122 numbers. How can I determine which are the remaining 763 numbers. In other words, " not equal to" the 359 numbers. Auto filter won't work; I can't copy in the 359 numbers after using the "Does Not Equal" filter in the Custom box from the Drop-down Autofilter menu. I am stumped! Thanks to anyone who takes the time to answer. I'll keep a close eye on this post for responses. sam- Hide quoted text - Suggestion: wouldn't the unmatched items be produced more directly in Col.B by just changing the "x" to A1 in the 2nd formula as follows: Use=IF(ISNA(VLOOKUP(A1,$D1$:$D$9,1,FALSE)),A1,"") .....AJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Having a pie chart ignore data fields equal to "0" | Excel Discussion (Misc queries) | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
sort data rows "greater than or equal" criteria in another cell | Excel Worksheet Functions |