Compare 2 Sets of List Columns in Excel 2013

Posted on 2013-11-06
Last Modified: 2013-11-08
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.

Question by:fireguy1125
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 81

Expert Comment

ID: 39628523
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.

Author Comment

ID: 39628600
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.
LVL 81

Expert Comment

ID: 39628648
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39630268
Thanks Brad, I sent you an e-mail with the spreadsheet.
LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39632256
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.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
        End With
        Range(rg.Cells(2, 3), rg.Cells(rg.Rows.Count, 3)).FormulaR1C1 = "=RC[-2] & ""|"" & RC[-1]"
    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)
    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)
    rg2.Cells(1, 1).Resize(j, 4).Value = v2New
End With
End Sub

Open in new window


Author Closing Comment

ID: 39633394
Thank you so much, worked perfect!

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question