COmpare COlumns Macro Closed

Question

Hello,

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!!! 

Thank you,
Justin

rizvisa1 · Answer

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.

Justin · Answer

Hey, RizVisa...

The data is similar to this:

LIST 1       LIST 2

HAZ8371	WAY7919
WAY7918	HVM1798
WAY7919	NBU8091
ASH7389	ASH7389
ASH7391	ASH7391
GHA7909	GHA7909
GHA7912	GHA7912
ASH7390	HIG8720
HAZ8372	ZYM8109
GHA7910	JKI8908
GHA7911	HGL8991
	AWE2891
	KIO8099
	GHT6738
	AWE2892
	GHT6739

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

rizvisa1 · Answer

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

COmpare COlumns Macro

3 responses

Similar discussions

Subscribe To Our Newsletter!