Reducing a List by Eliminating Entries in it Found in 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 basically needs to be the same as list B EXCEPT with any entries in
it 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. one minor technicality, both lists have identical entries in them, i.e.
list A might have dog in it 15 times, etc..

 

[Ralph]

Steve, thanks for writing!

Good Question - list C can contain cat one or five times, one time is
preferred but i can deal with duplicate entries ... the main objective to
create list C is to strip out of list B any entries that are also found in
list A ...

list A is in column A, list B is in column B and ideally list C will compile
in column C ... the lists might be sorted alphabetically but not sure, does
that matter in terms of developing a solution?

THANKS!

 

[Ralph]

... to further elaborate on your first question, ideally a routine would
initially eliminate duplicate entries found in column A and then do the same
for column B, then some sorting/removal macro would be applied ... but again,
i can deal with the duplicate entries if i have to ...

 

[Jacob Skaria]

Hi Ralph

Try the below macro. Assume your data starts in row1 ..

Sub Extract()
Dim c As Range, lngRow As Long
For Each c In Range("B1:B" & Cells(Cells.Rows.Count, "B").End(xlUp).Row)
If WorksheetFunction.CountIf(Range("A:A"), c.Text) = 0 Then _
lngRow = lngRow + 1: Range("C" & lngRow) = c.Text
Next
End Sub


If this post helps click Yes

 

[Ralph]

Jacob,

list a is in column a, list b is in column b, will your macro work like
that? if so, HOW DO I use that code? sorry, am clueless on how to do that
part

 

[Ralph]

figured it out, works GREAT, THANKS!!!

Hi Ralph

Try the below macro. Assume your data starts in row1 ..

Sub Extract()
Dim c As Range, lngRow As Long
For Each c In Range("B1:B" & Cells(Cells.Rows.Count, "B").End(xlUp).Row)
If WorksheetFunction.CountIf(Range("A:A"), c.Text) = 0 Then _
lngRow = lngRow + 1: Range("C" & lngRow) = c.Text
Next
End Sub


If this post helps click Yes