Compare Lists and Add Missing Items

[helpexcel]

Hi,

Is there a way I can compare the items in Column B to those in Column A. Then any item in Column A that's not in Column B would be added to the next available row in Column B.

Thanks!

 

[Fluff]

How about

Sub CompareCols()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("B2", Range("B" & Rows.count).End(xlUp))
         If .exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("B" & Rows.count).End(xlUp).Offset(1).Resize(.count).Value = Application.Transpose(.keys)
   End With
End Sub

 

[helpexcel]

It's telling me that C1 = Nothing. I'm guessing that C1 is supposed to equal the items in column A?

 

[Fluff]

In what way is it telling you that Cl =nothing?

 

[helpexcel]

when I hover over it in the code, and in the box at the bottom.

 

[Fluff]

Where has the code got to when you get that?

 

[helpexcel]

It errors on that line. I did make the one change to include the worksheet, because the lists are on different sheets.

For Each Cl In Worksheets("Sheet1").Range("A2", Range("A" & Rows.count).End(xlUp))

 

[Fluff]

In that case you need to qualify the second part of the range

For Each Cl In Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp))

 

[helpexcel]

AH!! Works great, Thanks!!

 

[Fluff]

Glad to help & thanks for the feedback