Exact function

[Jim Berglund]

I've created a simple spreadsheet.

Column A is a list of 15,034 used Work Order reference numbers (many values
are missing);

Column C is a complete list (25,000 WO 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 column D.

It gives me nothing but FALSE values, even though about half the numbers
match.
BTW, I do get a TRUE value if I use =EXACT(C21,A1), so it may be an array
problem...


Please help...
Thanks,
Jim Berglund

 

[Niek Otten]

Hi Jim,

Look here:

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've created a simple spreadsheet.
|
| Column A is a list of 15,034 used Work Order reference numbers (many values
| are missing);
|
| Column C is a complete list (25,000 WO 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 column D.
|
| It gives me nothing but FALSE values, even though about half the numbers
| match.
| BTW, I do get a TRUE value if I use =EXACT(C21,A1), so it may be an array
| problem...
|
|
| Please help...
| Thanks,
| Jim Berglund
|
|
|

 

[Dave Peterson]

Check your previous thread.

I've created a simple spreadsheet.

Column A is a list of 15,034 used Work Order reference numbers (many values
are missing);

Column C is a complete list (25,000 WO 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 column D.

It gives me nothing but FALSE values, even though about half the numbers
match.
BTW, I do get a TRUE value if I use =EXACT(C21,A1), so it may be an array
problem...

Please help...
Thanks,
Jim Berglund

 

[Ron Coderre]

Try something like this:

With
A10:A16000 containing WORef's (some missing)
A1: WORef

G10:G25000 containing a list of ALL WORef (none missing)
G10: WORefALL

Then
G1: TestMissing (or any non-column heading title or blank)
G2: =ISERROR(MATCH(G11,$A$11:$A$16000,0))

Select G1:G25000

Then...From the Excel main menu:
<data><filter><advanced filter>
List Range: $G$10:$G$25000
Criteria Range: $G$1:$G$2
Click the [OK] button

That will filter the complete list to only show missing numbers.

If you want to put that list in another range...(eg beginning in J10)
J10: WORefALL

Follow the above instructions, and...
Check: Copy to another location
Copy to: $J$10
Click the [OK] button

That will put the list of missing items in the range beginning with J10.

Note: either way, it will take several seconds to extract/filter the data,
but it's better than cluttering up your workbook with thousands of use-once
formulas that you'll just need to delete.


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP