Solved

Compare 2 Sets of List Columns in Excel 2013

Posted on 2013-11-06
6
379 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 80

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 80

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

746 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

11 Experts available now in Live!

Get 1:1 Help Now