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

 

[Steve Yandl]

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]

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

 

[Steve Yandl]

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]

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