Unique list for each of the two compared columns

[wfvdc]

All,

I have searched and search and not found what I am looking for. But I khow there has to be an answer. I am routinely comparing two lists "Original" vs "New". I worte an VBA macro to apply column headers, make named ranges from the ranges, and then use conditional formatting to color the uniques red and the duplicates green and autofilter. The goal is to find all the uniques that are not on the other list. The issue I am having us incorporating a way to list all of the unique values on
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Original
[/TD]
[TD]New
[/TD]
[TD][/TD]
[TD]Unique on Original
[/TD]
[TD]Unique on New
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]1.625
[/TD]
[TD]2.3125
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1.625
[/TD]
[TD]2.25
[/TD]
[TD][/TD]
[TD]1.75
[/TD]
[TD]2.3125
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1.75
[/TD]
[TD]2.3125
[/TD]
[TD][/TD]
[TD]1.875
[/TD]
[TD]2.375
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1.875
[/TD]
[TD]2.4375
[/TD]
[TD][/TD]
[TD]2.125
[/TD]
[TD]2.4375
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]2.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.5
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2.125
[/TD]
[TD]2.5625
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.5626
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]2.25
[/TD]
[TD]2.625
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.625
[/TD]
[/TR]
</TBODY>[/TABLE]


I have been manually applying the filter to find the uniques and the copying, then unapplying the filter, goting to D2 and then pasting the vlaues. Repeat for the other columns.

In Excel 2010, I have not found a way to display all the uniques in the respective unique columns. I have tried Interior.colorindex, Interior.color, FormatConditions(1).color and colorindex. I have tried testing the cell to see which rule is true with some prior version excel code. I have even tried uinsg macro recorder.

Can someone provide me with some code or a some info on how to accomplish the listing.

I perfer anything that that is VBA driven or that can be inserted into a cell with VBA. Ideally, Anything that loops through the named ranges to find the identified uniques and then puts them in the appropriate column.

Thanks,
Bill

 

[JLGWhiz]

This will put the unique items from column A into column D and the unique items from column B into column E.

Sub uniquorn()
Dim sh As Worksheet
Set sh = Sheets(1) 'Edit sheet name
    For Each c In Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
        If Application.CountIf(sh.Range("B:B"), c.Value) = 0 Then
            sh.Cells(Rows.Count, 4).End(xlUp)(2) = c.Value
        End If
    Next
    For Each r In Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
        If Application.CountIf(sh.Range("A:A"), r.Value) = 0 Then
            sh.Cells(Rows.Count, 5).End(xlUp)(2) = r.Value
        End If
    Next
End Sub

 

[wfvdc]

Worked Perfectly. I didn't mention at the time, but I am creating the sheet tab names with vba based on input variables for the named ranges. However, all I had to do was modify

Set sh = Sheets(1) 'Edit sheet name

to

Set sh = ActiveWorkbook.Sheets(NewSheetName)

Thank you so much for your code and time!

 

[JLGWhiz]

You're Welcome,
Regards, JLG