Remove duplicate and original

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Backdoor Cover - 09 Jun 2006 00:11 GMT

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

Reply to this Message

Dave Peterson - 09 Jun 2006 00:57 GMT

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)

> I have two lists of Client Accounts. One with 45,000 rows and the other
> is 45,800 rows.
[quoted text clipped - 13 lines]
> Backdoor Cover's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19842
> View this thread: http://www.excelforum.com/showthread.php?threadid=550166

Signature

Dave Peterson

Reply to this Message

Backdoor Cover - 09 Jun 2006 01:25 GMT

Thanks that worked great

Reply to this Message

Backdoor Cover - 09 Jun 2006 01:27 GMT

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?

Signature

Backdoor Cover

Reply to this Message

Peo Sjoblom - 09 Jun 2006 01:37 GMT

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

adapt to fit

Signature

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

> 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?

Reply to this Message