A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

formula to find "missing" data



 
 
Thread Tools Display Modes
  #1  
Old August 31st 08, 07:45 PM posted to microsoft.public.excel.worksheet.functions
LEG
external usenet poster
 
Posts: 16
Default 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  
Old August 31st 08, 08:22 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default 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  
Old September 1st 08, 02:27 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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 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  
Old September 9th 08, 04:38 PM posted to microsoft.public.excel.worksheet.functions
LEG
external usenet poster
 
Posts: 16
Default 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  
Old September 9th 08, 04:43 PM posted to microsoft.public.excel.worksheet.functions
LEG
external usenet poster
 
Posts: 16
Default 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 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  
Old September 9th 08, 11:47 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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  
Old September 10th 08, 01:25 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.