creating outlier list in Excel 2003

[jonpur]

What is the best way to compare two worksheets A & B (containing account #'s)of a workbook and then create a list of those account #'s that are on A that do do not appear on worksheet B

 

[Andrew Poulsom]

Welcome to MrExcel.

If your account numbers are in column A on both worksheets you can use a formula like:

=COUNTIF(Sheet2!A:A,A2)=0

in row 2 of a spare column of Sheet1 and copy it down. AutoFilter for that column equals TRUE to get your list.

 

[Aladin Akyurek]

What is the best way to compare two worksheets A & B (containing account #'s)of a workbook and then create a list of those account #'s that are on A that do do not appear on worksheet B

Let Sheet1, A2:A100, house those A-accounts, Sheet2, A2:A400, those B accounts.

Sheet1

E1, control+shift+enter, not just enter:

=SUM(IF(1-(A2:A100=""),
   IF(1-ISNUMBER(MATCH(A2:A100,Sheet2!A2:A400,0)),1)))

E2, List

E3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$3:E3)<=$E$1,INDEX($A$2:$A$100,
    SMALL(IF(ISNUMBER(MATCH($A$2:$A$100,Sheet2!$A$2:$A$400,0)),"",
     ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($E$3:E3))),"")