Is there an Excel formula

That that I can use to find if values in one column are not in another.

For instance:

I have invoice numbers in column A and B. I want to find out which invoices in column A are NOT in column B. I hope this makes sense.

TIA

Re: Is there an Excel formula

can you just sort them by the columns that A and B ar in. You can sort s it so that is sorts by the invoice that column a is in and then the column that B is then, so then all of the invoices that are in column B and not in Column A would be at the bottom....

Message edited 4/26/2007 11:22:57 AM.

Re: Is there an Excel formula

not so much a formula, but you can do Control + F to FIND, or just go under Edit, and hit FIND, then enter the value (data) of what it is you're looking for

HTH

Message edited 4/26/2007 11:21:30 AM.

Re: Is there an Excel formula

do a vlookup - =vlookup(value, range, number of cells, "false")

Let me know if you want to email me the spreadsheet and I'll add it

or jsut use the formula function

Re: Is there an Excel formula

Use a V lookup like Red suggested....

ETA: or you can use COUNTIF

Message edited 4/26/2007 11:40:28 AM.

Re: Is there an Excel formula

Try this:

You need 3 columns
Column A has all the invoices numbers
Column B is the 2nd list of invoices numbers

If you want to see if Column B is missing any invoices that are in column A, then:

In Column C type:
=VLOOKUP($A1,$B$1:$B$8,TRUE,FALSE)

You can copy this formula for all of column C. If there is a number in in Col.A that is not in Col.B, then you should get a #N/A.

This assumes your list is only 8 rows and starts in row 1 and ends in row 8 (for example purposes). These numbers would change depending on the size of your list.

HTH!!! I tried it with a few sample numbers and it worked for me.

Re: Is there an Excel formula

Countif is actually easier.

In Column C, type

=COUNTIF($B$1:$B$5,$A1)

If the numbers match, you'll get a 1
If the numbers don't match, you'll get a 0

Again, the range will change based on the length of your list.

Re: Is there an Excel formula

Thanks Guys