What I am trying to do is come up with a macro to compare 2 columns of data in sheet 1, columns A & B. One is a master list, and another is a list that has values contained in the master list. I would like to have a macro to compare the two columns and show the values in a third column which do not match the master list (The master list is in Column A). I had a previous macro to attempt this, but it is not working properly. An example of a typical cell is: HAW3157, a three letter prefix with a 4 number suffix without spaces. The macro that I was previously using for simple numbered data was:
Sub DupeRemovalTwoLists()
Dim LR As Long, LC As Long
LC = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 5
LR = Range("B" & Rows.Count).End(xlUp).Row
Cells(1, LC) = "Key"
Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,C1,0))"
Cells(1, LC).AutoFilter
Cells(1, LC).AutoFilter Field:=1, Criteria1:="True"
Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).ClearContents
Cells(1, LC).AutoFilter
Columns(LC).ClearContents
Range("B2:B" & LR).Sort [B1], xlAscending
MsgBox LR - Range("B" & Rows.Count).End(xlUp).Row & " items were deleted"
End Sub
If anyone could help me, I'd GREATLY APPRECIATE IT!!!
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
There is one master list, being the list on the right. The problem that I'm trying to overcome is to take out the values in List 2 that are in List 1, or at least highlight them. I have hundreds of cells like these with one "master list" and then another list that comprises lots of data. If you notice in this example, List 2 has values that are in List 1 - I need a macro to compare the two columns and remove the duplicate values in List 2 that are in List 1.
Any help would be GREATLY appreciated and would make my life much easier!
Thank you,
Justin
Assumptions
1. The values to be deleted are in column B
2. every column that has some data, has a column header
3. The master list is in column A
Sub UnMatchedListSecond()
Dim lMaxRows As Long 'max rows in list 2
Dim iMaxColumn As Integer 'last column on row 1 with no header. to be used for calcultion
Dim lThisRow As Long ' a temp row counter
''last column on row 1 with no header. to be used for calcultion
iMaxColumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1
'max rows in list 2
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Cells(1, iMaxColumn) = "NEW VALUE"
Cells(2, iMaxColumn).Select
ActiveCell.Formula = "=COUNTIF(A:A,""="" & B2)"
Cells(2, iMaxColumn).Select
Selection.AutoFill Destination:=Range(Cells(2, iMaxColumn), Cells(lMaxRows, iMaxColumn)), Type:=xlFillDefault
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
For lThisRow = lMaxRows To 2 Step -1
If (Cells(lThisRow, iMaxColumn) > 0) Then
Cells(lThisRow, "B").Delete Shift:=xlUp
End If
Next lThisRow
Range(Cells(1, iMaxColumn), Cells(lMaxRows, iMaxColumn)).Clear
End Sub
