Microsoft KB Archive/103708

From BetaArchive Wiki

XL: Excluding a List of Records in Extract



The information in this article applies to:


  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 4.0a
  • Microsoft Excel for OS/2, version 3.0





SUMMARY

When you extract records from a database in Microsoft Excel, you specify criteria that you want the extracted records to meet. In cases in which you have a list that you want to exclude in your results, you can use the MATCH and ISNA functions as a computed criterion.

To exclude a list, use the following formula


   =ISNA(MATCH(<firstcell>,<excludelist>,0)) 



where <firstcell> is the first cell in the field of your database that you want to extract, and <excludelist> is a list of entries that you want to exclude.



MORE INFORMATION

In the following example, to extract all of the records EXCEPT those that match C2:C4, use the ISNA and MATCHfunctions as a computed criterion.


   Database          ExcludeList       Criteria
   ----------------------------------------------------------

   A1: Name          C1:               F1: CompCrit
   A2: Ann           C2: Ann           F2: =ISNA(MATCH(A2,$C$2:$C$4,0))
   A3: Fred          C3: Bob
   A4: Bob           C4: Nikki
   A5: Kristi
   A6: Nikki 



NOTE: Your reference to the list of entries that you want to exclude must be an absolute reference or a defined name.

Note that when you specify a computed criterion, the cell above the formula cannot contain the actual field name; it must either be blank or contain a different text string.



REFERENCES

"User's Guide 1," version 4.0, pages 330-337
"Function Reference," version 4.0, pages 248, 267

Additional query words: 4.00a howto exclusionary exception exceptional XL4 XL3

Keywords :
Version : WINDOWS:3.0,4.0,4.0a; OS/2:3.0; MACINTOSH:3.0,4.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo
Technology :


Last Reviewed: April 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.