Compare 2 Sets of List Columns in Excel 2013

I have 2 sets of data, in this case a printer name and driver name.  I got these from 2 different print servers.  I need compare these 2 sets of data to determine which printer drivers don't match between the two servers.

I have 4 columns

1 print server data is columns A and B
the othe rprint server data is columns D and E
Column A - Printer Name
Column B - Driver Name
Column C - Printer Name
Column D - Driver Name

I would like to compare the data and indicate in column G, which printer drivers don't match between servers and the printer name.

I do not have the same number of printers and their drivers between columns A&B and C&D.

I know this can be done in Excel, just don't know how.

Thanks
LVL 1
fireguy1125Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Here is a macro that will sort two lists in ascending order first by printer and then by driver. It will then align the two lists by inserting blanks as required. Matching combinations will be present on the same line in both lists. Unmatched combinations will be paired with blanks in the other list. The macro uses array transfer for speed--it's enormously faster than inserting blank cells in the worksheet.

The macro assumes that the first list is in columns A:B and the second list is in E:F. Columns C:D and G:H must be blank, as the macro uses them for scratch space.
Sub SortData()
'List1 should be in columns A:B
'List2 should be in columns E:F
Dim rg As Range, rg1 As Range, rg2 As Range
Dim sCol1 As String, sCol2 As String
Dim i As Long, j As Long, k As Long, n1 As Long, n2 As Long
Dim v1 As Variant, v2 As Variant, v1New As Variant, v2New As Variant
'Application.ScreenUpdating = False
With ActiveSheet
    Set rg1 = .Cells(2, 1)
    Set rg1 = Range(rg1, .Cells(.Rows.Count, rg1.Column).End(xlUp)).Resize(, 4)
    Set rg2 = .Cells(2, 5)
    Set rg2 = Range(rg2, .Cells(.Rows.Count, rg2.Column).End(xlUp)).Resize(, 4)
    sCol1 = "C" & rg1.Columns(3).Column
    sCol2 = "C" & rg2.Columns(3).Column
    For Each rg In Union(rg1, rg2).Areas
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=rg.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=rg.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                
            .SetRange rg
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range(rg.Cells(2, 3), rg.Cells(rg.Rows.Count, 3)).FormulaR1C1 = "=RC[-2] & ""|"" & RC[-1]"
    Next
    Range(rg1.Cells(2, 4), rg1.Cells(rg1.Rows.Count, 4)).Formula = _
        "=ROWS(D$3:D3)+COUNTIF(G:G,""<="" & C3)-SUMPRODUCT(COUNTIF(G:G,C$3:C3))"
    Range(rg2.Cells(2, 4), rg2.Cells(rg2.Rows.Count, 4)).Formula = _
        "=ROWS(H$3:H3)+COUNTIF(C:C,""<="" & G3)-SUMPRODUCT(COUNTIF(C:C,G$3:G3))"
    n1 = rg1.Rows.Count
    n2 = rg2.Rows.Count
    v1 = rg1.Value
    v2 = rg2.Value
    ReDim v1New(1 To n1 + n2, 1 To 4)
    ReDim v2New(1 To n1 + n2, 1 To 4)
    v1New(1, 1) = v1(1, 1)
    v1New(1, 2) = v1(1, 2)
    v2New(1, 1) = v2(1, 1)
    v2New(1, 2) = v2(1, 2)
    For i = 2 To n1
        j = v1(i, 4) + 1
        v1New(j, 1) = v1(i, 1)
        v1New(j, 2) = v1(i, 2)
    Next
    rg1.Cells(1, 1).Resize(j, 4).Value = v1New
    For i = 2 To n2
        j = v2(i, 4) + 1
        v2New(j, 1) = v2(i, 1)
        v2New(j, 2) = v2(i, 2)
    Next
    rg2.Cells(1, 1).Resize(j, 4).Value = v2New
End With
End Sub

Open in new window

0
 
byundtCommented:
I suggest a formula like the following:
=IF(AND(COUNTIF(C:C,A2)>0,COUNTIFS(C:C,A2,D:D,B2)=0),A2 & " driver not found in column D","")

It confirms that printer in A2 is shown in column C, but all the drivers are different from the one in B2.
PrinterDriverComparisonQ-2828728.xlsx
0
 
fireguy1125Author Commented:
Thanks, is there any way to highlight the results or somehow match the rows by printer name for both sets of data, it's returning the results, but then I have to scroll up and down to find the data in each pair of columns.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
byundtCommented:
You can help yourself out by sorting your two lists, first by driver and then by printer name.

You can highlight drivers with no match using Conditional formatting with formula criteria like:
=AND(COUNTIF($C:$C,$A2)>0,COUNTIFS($C:$C,$A2,$D:$D,$B2)=0)           for columns A & B
=AND(COUNTIF($A:$A,$C2)>0,COUNTIFS($A:$A,$C2,$B:$B,$D2)=0)           for columns C & D

Could you post your file? I'd like to try aligning the two lists.
0
 
fireguy1125Author Commented:
Thanks Brad, I sent you an e-mail with the spreadsheet.
0
 
fireguy1125Author Commented:
Thank you so much, worked perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.