Using the EXACT Function

[Jim Berglund]

I've created a simple spreadsheet.

Column A is a list of 15034 used Work Order reference numbers (many values
are missing); Column C is a complete list (25,000 numbers).

I want to get a separate list of unused numbers, but can't get the EXACT
function to work. ( I believe it should place a TRUE value in column D when
the numbers match.)

I've used:
=OR(EXACT(C1,$A$1:$A$15034)), which I replicated down the column.


Please help...
Thanks,
Jim Berglund

 

[Mark Lincoln]

I don't think EXACT will work. Try a VLOOKUP:

in D1, type

=VLOOKUP(C1,$A$1:$A$15034,1,FALSE)

in E1, type

=IF(ISNA(D1),C1,"")

and replicate both formulas down to Row 25000.

The numbers in Column E will then contain your unused numbers. Copy
that column, Paste Special (Values), and then you can delete the
formulas in Column D.

Mark Lincoln

 

[Dave Peterson]

Maybe you could use =match() to check to see if it's there:

=isnumber(match(c1,$a$1:$a$15034,0))

 

[Jim Berglund]

Thanks, It worked perfectly!
Jim Berglund

I don't think EXACT will work. Try a VLOOKUP:

in D1, type

=VLOOKUP(C1,$A$1:$A$15034,1,FALSE)

in E1, type

=IF(ISNA(D1),C1,"")

and replicate both formulas down to Row 25000.

The numbers in Column E will then contain your unused numbers. Copy
that column, Paste Special (Values), and then you can delete the
formulas in Column D.

Mark Lincoln

 

[Mark Lincoln]

Dave's suggestion could be modified to do the whole deal in one
column.

In D1:

=IF(ISNUMBER(MATCH(C1,$A$1:$A$15034,0)),"",C1)

and replicate down, giving you the unused values. Then Copy, Paste
Special (Values) and you're done.

Mark Lincoln