Compare data from 2 columns pull unique rows

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
539
I would like to compare 2 separate columns and list out unique rows in another sheet.

Code:
Column 1         Column 2    Column 3       Column 4      Column 5
MOW204000235	1	       No	      No	Vehicle Maint.
MOW204000235	1	       Yes            No	Driver Fitness
MOW204000235	1	       No	      Yes        Vehicle Maint.
OH3260013363	3	       Yes            No	Driver Fitness
OH3260013363	3	       Yes            No	Driver Fitness
MOW204000155	1	       No	      Yes        Vehicle Maint.
MOW204000155	1	       No	      No	Unsafe Driving
MOW204000155	1	       Yes           Yes        Driver Fitness
MOW204000155	1	       No	      Yes        Driver Fitness
I would like the code to compare Column 1 and Column 5 and list on another sheet the data from those rows.

Results
Code:
Column 1            Column 2    
MOW204000235     Vehicle Maint.
MOW204000235     Driver Fitness
OH3260013363      Driver Fitness
MOW204000155     Vehicle Maint.
MOW204000155     Unsafe Driving
MOW204000155     Driver Fitness
I can find tons of stuff that compares the two rows as if they contained the same data, but nothing if contains different data. Also, ignoring the 3 columns in the creates other issues. Thanks for the help.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub MoveDupes()
   Dim Cl As Range
   Dim Valu As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Valu = Cl.Value & "|" & Cl.Offset(, 4).Value
         If Not .Exists(Valu) Then .Add Valu, Array(Cl.Value, Cl.Offset(, 4).Value)
      Next Cl
      Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("A1").Resize(.Count).Value = Application.Transpose(.keys)
      Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("A1").Resize(.Count).TextToColumns Destination:=Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("A1"), _
         DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1))
   End With
End Sub
Change sheet name in red to match your output sheet
 

KKaren

New Member
Joined
Jan 13, 2018
Messages
33
If I understand the assignment, and assuming that your info is in columns from "A" to "E", I believe this should do the trick although you will probably have to make some adjustments...

Code:
Sub CompareColumns()

    Dim LastRow As Long, x As Long
    
    ActiveSheet.Columns("A:E").Copy
        Worksheets.Add after:=ActiveSheet
        ActiveSheet.Range("A1").PasteSpecial
        
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("F1").Value = "Combined info"
    
    For x = 2 To LastRow
        Range("F" & x).Value = Range("A" & x).Value & Range("E" & x).Value
    
    Next x
    
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=6, Header:=xlYes
    
    ActiveSheet.Range("B:D,F:F").EntireColumn.Delete
    
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top