Lemme see if I can explain this adequately...lol
I have two sheets with account numbers on them. One sheet is ALL acct #'s
and the 2nd is USED acct #'s. I would like to (on sheet 3) compare both
lists of acct #'s and then list the ones that are not on both sheets. Hope
this makes sense.... Any help is GREATLY appreciated!!

frustratedwthis wrote:
Lemme see if I can explain this adequately...lol
I have two sheets with account numbers on them. One sheet is ALL acct #'s
and the 2nd is USED acct #'s. I would like to (on sheet 3) compare both
lists of acct #'s and then list the ones that are not on both sheets. Hope
this makes sense.... Any help is GREATLY appreciated!!

Here is an idea....

If Sheet1 has all account #'s then there aren't any on Sheet1 that
aren't on Sheet2. So, on Sheet3, I would copy the full list of account
numbers from Sheet1, and paste them in column A. Then, in column B
write a formula similar to this:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$A$5,1,0)),"NOT USED","USED")

It will try to lookup an account number in Sheet2 (the used account
numbers). If it doesn't exist, then it will create an error in the
vlookup, and the If statement will produce a "NOT USED". If there
isn't an error in the Vlookup, then it will put a "USED" because it was
able to look it up. The next step would be to insert a row at the top
of Sheet3, and put in some headings so that you can AutoFilter the
data. Column A would be Acct #'s. Column B would be "Exist", or
something to that effect. Then select all the data in sheet3
(including the headings), and DataFilterAutofilter. Click on the
little arrow on the heading of column B, and there should just be a
"NOT USED" or a "USED". Select the "NOT USED" and the resulting list
should be those that are on Sheet1, but not Sheet2. Obviously, do a
check to see if it is working correctly!

I figured out how to show only the acct numbers that are not used. My
dilemma is now this...
they are all spread out in column C. One acct no might be in row 4 and the
next might not be until row 25. Do you know how I can have all the acct
numbers listed in column D, starting with row 1?

"Paul" wrote:


frustratedwthis wrote:
Lemme see if I can explain this adequately...lol
I have two sheets with account numbers on them. One sheet is ALL acct #'s
and the 2nd is USED acct #'s. I would like to (on sheet 3) compare both
lists of acct #'s and then list the ones that are not on both sheets. Hope
this makes sense.... Any help is GREATLY appreciated!!

Here is an idea....

If Sheet1 has all account #'s then there aren't any on Sheet1 that
aren't on Sheet2. So, on Sheet3, I would copy the full list of account
numbers from Sheet1, and paste them in column A. Then, in column B
write a formula similar to this:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$A$5,1,0)),"NOT USED","USED")

It will try to lookup an account number in Sheet2 (the used account
numbers). If it doesn't exist, then it will create an error in the
vlookup, and the If statement will produce a "NOT USED". If there
isn't an error in the Vlookup, then it will put a "USED" because it was
able to look it up. The next step would be to insert a row at the top
of Sheet3, and put in some headings so that you can AutoFilter the
data. Column A would be Acct #'s. Column B would be "Exist", or
something to that effect. Then select all the data in sheet3
(including the headings), and DataFilterAutofilter. Click on the
little arrow on the heading of column B, and there should just be a
"NOT USED" or a "USED". Select the "NOT USED" and the resulting list
should be those that are on Sheet1, but not Sheet2. Obviously, do a
check to see if it is working correctly!