Home 
Search 
Today's Posts 
#1




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




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 arrayentered 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




formula to find "missing" data
Another play using simple n fast nonarray 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 C22 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




formula to find "missing" data
Thanks  it worked!
 LEGdenmark "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 arrayentered 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




formula to find "missing" data
Thanks  this one worked as well.
 LEGdenmark "Max" skrev: Another play using simple n fast nonarray 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 C22 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




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.  LEGdenmark 
#7




formula to find "missing" data
On Tue, 9 Sep 2008 08:38:28 0700, LEG wrote:
Thanks  it worked!  LEGdenmark 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) 