Remove duplicate and original

B

Backdoor Cover

I have two lists of Client Accounts. One with 45,000 rows and the othe
is 45,800 rows.

All of the 45,000 in Column A is in the 45,800 in Column B.

I only want that 800 unique accounts from Column B.

Doing an If(countif(.. crashes my PC. Is there a better way tro fin
that 800?

Thanks
 
D

Dave Peterson

This may slow things down, but I think lots of these are not as slow as lots of
=countif()'s.

In C1:
=isnumber(match(b1,a:a,0))
(dragged down)

If it's true, then b1 appears in column A.

Then I'd recalculate and convert those formulas to values.

Then filter by column C to show the falses.

(the filter should work faster with values--not formulas)
 
B

Backdoor Cover

the forumla: =ISNUMBER(MATCH(A2,B:B,0))

could I substitute ROWS instead of B:B somehow so it was only looking
at 400 rows instead of 65K?
 
P

Peo Sjoblom

=ISNUMBER(MATCH(A2,$B$2:$B$402,0))

adapt to fit


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Backdoor Cover"
 
Top