Previous Topic Next Topic

COmpare COlumns Macro

Closed
Justin - Apr 28, 2010 at 03:01 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 1, 2010 at 04:15 PM
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

3 replies

Answer 1 / 3
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 28, 2010 at 03:13 PM
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.
0
Answer 2 / 3
Justin
May 1, 2010 at 03:16 PM
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
0
Answer 3 / 3
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 1, 2010 at 04:15 PM
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
0