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!

 

[helpexcel]

Spoke a little too soon. It errors out if the lists are identical. Is there a line I can add that says this is OK? NEVERMIND, looks like it was user error. sorry, and thanks again!

 

[Fluff]

Just change the last line to

      [COLOR=#ff0000]If .count > 0 Then [/COLOR]Range("B" & Rows.count).End(xlUp).Offset(1).Resize(.count).Value = Application.Transpose(.keys)

 

[helpexcel]

If I wanted to use this on multiple lists, would i need to change CreateObject("scripting.dictionary")? I changed C1, but I'm still getting the same list.

 

[Fluff]

What do you mean by "multiple lists"?

 

[helpexcel]

THANKS for all your help! I figure it out, i forgot to change one of the other variables. I'm actually comparing multiple sets of list, 2 at a time.

What if i wanted to compare A to B and if A was on B then delete it from A? would I make this change, plus flip the code so B comes first?
If Not .exists(C2.Value) Then .Delete C2.Value, Nothing

 

[Fluff]

Do you mean you want to delete the cell from the sheet?

 

[helpexcel]

Yes, delete it from Column A.

 

[Fluff]

Try

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, Cl
      Next Cl
      For Each Cl In Range("B2", Range("B" & Rows.count).End(xlUp))
         If .Exists(Cl.Value) Then
            .Item(Cl.Value).Delete xlShiftUp
            .Remove Cl.Value
         End If
      Next Cl
      If .count > 0 Then Range("B" & Rows.count).End(xlUp).Offset(1).Resize(.count).Value = Application.Transpose(.keys)
   End With
End Sub

 

[helpexcel]

This brings everything over like the first code. For this one i just want to say if the item in column A is also in coulmn B, then just delete it from column A.

 

[Fluff]

In that case simply delete this line

If .count > 0 Then Range("B" & Rows.count).End(xlUp).Offset(1).Resize(.count).Value = Application.Transpose(.keys)