Reducing a List by Eliminating Entries from Another List

[Ralph]

Hi,

I have several massive lists that I need some excel kung fu help with. Best
way to explain the challenge is with an example. I have lists A & B and want
to make list C as follows:

list A - dog, cat, ball, orange, cloud, peppers
list B - hockey, beer, smart, rain, ball, ocean

list C needs to be list B with any entries that are also in list A removed,
in this case "ball" needs to be removed, so list C becomes

list C - hockey, beer, smart, rain, ocean

comprende'? can anyone PLEASE HELP with this?

Thanks!

p.s. these lists are 5 to 10k worth of entries long in columns

 

[Peggy Shepard]

Hi Ralph,

=IF(A1<>"",IF(COUNTIF(A:B,A1)>1,"",A1),IF(INDIRECT(ADDRESS(ROW()-MATCH("",A:A,-1),2))<>"",IF(COUNTIF(A:B,INDIRECT(ADDRESS(ROW()-MATCH("",A:A,-1),2)))>1,"",INDIRECT(ADDRESS(ROW()-MATCH("",A:A,-1),2))),""))

This assumes that list A is in column A, list B is in column B, and that the
only blank cells are at the end of each list - otherwise will need a way to
trap where each list ends, a unique qualifier.

Peggy

 

[Ralph]

Peggy,

Thanks for writing! I tried using your formula and it appears to be working
but it is creating an added/unexpected problem that I hadn't anticipated.
The problem is that in column A there are duplicate entries, i.e. dog, beer,
beer, beer, cat ...

Your formula would probably work if I had a way to first eliminate these
duplicate entries in column A. Can you suggest a way to do this OR revise
the formula you gave me to accomodate for this complexity?

As it stands now, where there are duplicate entries in column A, such as
beer, beer, beer NOT A SINGLE instance of beer will show up in column C which
is not what I need.

Do you follow me?

THANKS!
Ralph

 

[Ralph]

ACTUALLY, just to be sure, did you want me to paste this formula in cell C1
and copy it down column C? because if so, now that I look at what it's doing
it is NOT giving me the list in column B minus what's in column A ... hmmm

 

[Bernd P]

Hello Ralph,

Select C1:C1000 and array-enter:
=Pstat("count",ISERROR(MATCH(B1:B1000,A1:A1000,0)),B1:B1000)

Pstat you can find here:
http://sulprobil.com/html/pstat.html

Regards,
Bernd

 

[Ralph]

thanks Bernd, column c just ended up with a bunch of "#NAME?", don't think it
worked or I didn't do something right, please advise!

 

[Ralph]

also, this formula seems to really be bearing down on my ram and that was
only for 1000 rows, i need something to handle up to 10,000 rows, etc... if
possible, thanks!

 

[Ralph]

is there anybody out there who can help me with this issue please???