If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Excel  Golf  how to display "2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell  Steve Kay  Excel Discussion (Misc queries)  2  August 8th 08 01:54 AM 
Signature missing when "send to" > "mail recipient"  Jim Tortorelli  Excel Discussion (Misc queries)  2  September 21st 07 05:19 PM 
Missing data and "invalid references"  Jordlaw  Charts and Charting in Excel  0  September 11th 07 02:20 PM 
Scroll Bar missing "Control" tab in "Format Properties" dialog box  Peter Rooney  Excel Discussion (Misc queries)  5  August 24th 06 05:36 PM 
HELP on "left","right","find","len","substitute" functions  serene83  Excel Discussion (Misc queries)  5  June 27th 06 02:23 AM 