Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
Hi!
I am looking for a formula that can: I have a list of (nearly) identical numbers in two columns. In one column there are 100 numbers and in column 2 there are 90 numbers. Is there a formula or macro which can find the 10 numbers which are in column 1, but not in column 2 and list them in at third column? I am testing two databases that should have the same data, but doesn't. I have the 2007 version of Excel. LEG |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
On Sun, 31 Aug 2008 11:45:01 -0700, LEG wrote:
Hi! I am looking for a formula that can: I have a list of (nearly) identical numbers in two columns. In one column there are 100 numbers and in column 2 there are 90 numbers. Is there a formula or macro which can find the 10 numbers which are in column 1, but not in column 2 and list them in at third column? I am testing two databases that should have the same data, but doesn't. I have the 2007 version of Excel. LEG If your data is in A1:A100 & B1:B90, then this array-entered formula will list the entries in A that are not found in B. This is an **array** formula so must be entered with <ctrl<shift<enter. If you do it correctly, Excel will place braces {...} around the formula: =IF(ROWS($1:1)SUM(--ISNA(MATCH($A$1:$A$100,$B$1:$B$90,0))), "",INDEX($A$1:$A$100,LARGE(ISNA(MATCH( $A$1:$A$100,$B$1:$B$90,0))*ROW(INDIRECT("1:100")), ROWS($1:1)))) Fill down until you don't get any values returned. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
Another play using simple n fast non-array formulas
Assuming data in cols A and B, from row 2 down In C2: =IF(A2="","",IF(COUNTIF(B:B,A2),"",ROWS($1:1))) Leave C1 blank In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))+1)) Copy C2:D2 down to the last row of data expected in col A, eg down to D1000? Minimize/hide col C. Col D will return the list of items in col A not found in col B, with results all neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "LEG" wrote: I am looking for a formula that can: I have a list of (nearly) identical numbers in two columns. In one column there are 100 numbers and in column 2 there are 90 numbers. Is there a formula or macro which can find the 10 numbers which are in column 1, but not in column 2 and list them in at third column? I am testing two databases that should have the same data, but doesn't. I have the 2007 version of Excel. LEG |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
Thanks - it worked!
-- LEG-denmark "Ron Rosenfeld" skrev: On Sun, 31 Aug 2008 11:45:01 -0700, LEG wrote: Hi! I am looking for a formula that can: I have a list of (nearly) identical numbers in two columns. In one column there are 100 numbers and in column 2 there are 90 numbers. Is there a formula or macro which can find the 10 numbers which are in column 1, but not in column 2 and list them in at third column? I am testing two databases that should have the same data, but doesn't. I have the 2007 version of Excel. LEG If your data is in A1:A100 & B1:B90, then this array-entered formula will list the entries in A that are not found in B. This is an **array** formula so must be entered with <ctrl<shift<enter. If you do it correctly, Excel will place braces {...} around the formula: =IF(ROWS($1:1)SUM(--ISNA(MATCH($A$1:$A$100,$B$1:$B$90,0))), "",INDEX($A$1:$A$100,LARGE(ISNA(MATCH( $A$1:$A$100,$B$1:$B$90,0))*ROW(INDIRECT("1:100")), ROWS($1:1)))) Fill down until you don't get any values returned. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
Thanks - this one worked as well.
-- LEG-denmark "Max" skrev: Another play using simple n fast non-array formulas Assuming data in cols A and B, from row 2 down In C2: =IF(A2="","",IF(COUNTIF(B:B,A2),"",ROWS($1:1))) Leave C1 blank In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))+1)) Copy C2:D2 down to the last row of data expected in col A, eg down to D1000? Minimize/hide col C. Col D will return the list of items in col A not found in col B, with results all neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "LEG" wrote: I am looking for a formula that can: I have a list of (nearly) identical numbers in two columns. In one column there are 100 numbers and in column 2 there are 90 numbers. Is there a formula or macro which can find the 10 numbers which are in column 1, but not in column 2 and list them in at third column? I am testing two databases that should have the same data, but doesn't. I have the 2007 version of Excel. LEG |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
Welcome ..
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:56 xdemechanik --- "LEG" wrote in message ... Thanks - this one worked as well. -- LEG-denmark |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find "missing" data
On Tue, 9 Sep 2008 08:38:28 -0700, LEG wrote:
Thanks - it worked! -- LEG-denmark You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Signature missing when "send to" "mail recipient" | Excel Discussion (Misc queries) | |||
Missing data and "invalid references" | Charts and Charting in Excel | |||
Scroll Bar missing "Control" tab in "Format Properties" dialog box | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |