Reducing a List by Eliminating Entries in it Found in Another List
 

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

Reducing a List by Eliminating Entries in it Found in Another List
 

If list A does not contain "cat" but list B has "cat" listed 5 times, is
list C supposed to contain "cat' one time or five times?

How are the lists stored? The details are slightly different depending on
whether lists are in columns, rows, or distributed some other way.


Steve Yandl



"Ralph" wrote in message
...
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..

Reducing a List by Eliminating Entries in it Found in Another
 

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!
"Steve Yandl" wrote:

If list A does not contain "cat" but list B has "cat" listed 5 times, is
list C supposed to contain "cat' one time or five times?

How are the lists stored? The details are slightly different depending on
whether lists are in columns, rows, or distributed some other way.


Steve Yandl



"Ralph" wrote in message
...
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..

Reducing a List by Eliminating Entries in it Found in Another
 

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


"Steve Yandl" wrote:

If list A does not contain "cat" but list B has "cat" listed 5 times, is
list C supposed to contain "cat' one time or five times?

How are the lists stored? The details are slightly different depending on
whether lists are in columns, rows, or distributed some other way.


Steve Yandl



"Ralph" wrote in message
...
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..

Reducing a List by Eliminating Entries in it Found in Another List
 

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
---------------
Jacob Skaria


"Ralph" wrote:

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

Reducing a List by Eliminating Entries in it Found in Another
 

Give this a try.

'---------------------------------------------
Sub SiftList()
Dim rngA As Range
Dim rngB As Range
Dim vArray As Variant

Set rngA = Range("A1:A" & _
Cells(Cells.Rows.Count, "A").End(xlUp).Row)

Set rngB = Range("B1:B" & _
Cells(Cells.Rows.Count, "B").End(xlUp).Row)

Set oDicA = CreateObject("Scripting.Dictionary")
Set oDicB = CreateObject("Scripting.Dictionary")

For Each myCell In rngA.Cells
If Not oDicA.Exists(myCell.Value) Then
oDicA.Add myCell.Value, myCell.Value
End If
Next myCell

For Each myCell In rngB.Cells
If Not oDicA.Exists(myCell.Value) And _
Not oDicB.Exists(myCell.Value) Then
oDicB.Add myCell.Value, myCell.Value
End If
Next myCell

vArray = oDicB.Items
For i = 0 To oDicB.Count - 1
Cells((i + 1), 3).Value = vArray(i)
Next i

Set oDicA = Nothing
Set oDicB = Nothing
End Sub

'---------------------------------------------

Steve Yandl



"Ralph" wrote in message
...
.. 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 ...


"Steve Yandl" wrote:

If list A does not contain "cat" but list B has "cat" listed 5 times, is
list C supposed to contain "cat' one time or five times?

How are the lists stored? The details are slightly different depending
on
whether lists are in columns, rows, or distributed some other way.


Steve Yandl



"Ralph" wrote in message
...
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..

Reducing a List by Eliminating Entries in it Found in Another
 

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 :(

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Ralph" wrote:

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

Reducing a List by Eliminating Entries in it Found in Another
 

figured it out, works GREAT, THANKS!!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Ralph" wrote:

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