Remove duplicate and original

I have two lists of Client Accounts. One with 45,000 rows and the other 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 find that 800?

Thanks!

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)



Backdoor Cover wrote:
>
> I have two lists of Client Accounts. One with 45,000 rows and the other
> 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 find
> that 800?
>
> Thanks!
>
> --
> Backdoor Cover
> ------------------------------------------------------------------------
> Backdoor Cover's Profile: http://www.excelforum.com/member.php...o&userid=19842
> View this thread: http://www.excelforum.com/showthread...hreadid=550166

--

Dave Peterson

Thanks that worked great.

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?

=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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> Backdoor Cover
> ------------------------------------------------------------------------
> Backdoor Cover's Profile:
> http://www.excelforum.com/member.php...o&userid=19842
> View this thread: http://www.excelforum.com/showthread...hreadid=550166
>