Solved

Compare 2 Sets of List Columns in Excel 2013

Posted on 2013-11-06
6
389 Views
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.

Thanks
0
Comment
Question by:fireguy1125
  • 3
  • 3
6 Comments
 
LVL 81

Expert Comment

by:byundt
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.
PrinterDriverComparisonQ-2828728.xlsx
0
 
LVL 1

Author Comment

by:fireguy1125
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.
0
 
LVL 81

Expert Comment

by:byundt
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

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

Accepted Solution

by:
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.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
 
LVL 1

Author Closing Comment

by:fireguy1125
ID: 39633394
Thank you so much, worked perfect!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

937 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now