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]

Perfect, THANK YOU!!!

I changed this part of the code to .Item(Cl.Value).Clear because i have items under the list which would get messed up with the moving the deleted rows up.

 

[Fluff]

Glad to help & thanks for the feedback

 

[helpexcel]

Can i bother you one last time to help with code that would not only clear the cell in column A, but also those through Column D. So that if A5 is cleared, B5:D5 are also cleared.
I don't know if there is a way to copy and past items to move up so there are no blank spaces. My range for the list would be A5:D30.

 

[Fluff]

This will clear A:D

.Item(cl.Value).Resize(, 4).Clear

 

[helpexcel]

I tried tried to get creative with this by changing it to .Item(C1.Value).Selection.SpecialCells(xlCellTypeConstants, 4).ClearContents because I have formula in one of the columns. Didn't work.

 

[helpexcel]

I finally figure out that out. But i'm struggling with trying to copy the duplicates to sheet 3. I tried the code below but that didn't work. I would be running this code mutliple times so if i could get it to copy to next available row would be great. Playing with a bunch of different codes, at some point this stuff will make sense to me.

.Item(C1.Value).Resize(, 4).Copy
worksheets("sheet3").Range("A5").paste

 

[Fluff]

As this is a completely different question, you'll need to start a new thread.

 

[helpexcel]

@Fluff How do I lookup an item on 2 lists with out the ws3 overriding the ws2 inputs? basically changing it so that if it cant find the item from ws1 in ws2, then look in ws3.

With CreateObject("scripting.dictionary")      
.CompareMode = 1
        For Each Cl In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 2).Value
        Next Cl
        For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
         Next Cl
        For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 2).Value
        Next Cl
        For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
        Next Cl
End With

 

[Fluff]

As I said before, this is a totally different question, so you will need to start a new thread.

 

[helpexcel]

@Fluff I use this code a lot, it's so handy. I have noticed that it won't recognize that values are the same if one is all caps. So it sees "Apple" and "APPLE" as different. Is there a way to "fix" this?