Exact function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Berglund - 19 Apr 2007 20:20 GMT

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

Reply to this Message

Niek Otten - 19 Apr 2007 20:37 GMT

Hi Jim,

Look here:

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've created a simple spreadsheet.
|
[quoted text clipped - 18 lines]
| Thanks,
| Jim Berglund

Reply to this Message

Dave Peterson - 19 Apr 2007 20:37 GMT

Check your previous thread.

> I've created a simple spreadsheet.
>
[quoted text clipped - 18 lines]
> Thanks,
> Jim Berglund

Signature

Dave Peterson

Reply to this Message

Ron Coderre - 19 Apr 2007 20:50 GMT

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

> I've created a simple spreadsheet.
>
[quoted text clipped - 18 lines]
> Thanks,
> Jim Berglund

Reply to this Message