[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Compare 2 Sets of List Columns in Excel 2013

Posted on 2013-11-06
Medium Priority
452 Views
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
Question by:fireguy1125
[X]
###### 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
• 3
• 3

LVL 81

Expert Comment

ID: 39628523
I suggest a formula like the following:

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

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

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

LVL 1

Author Comment

ID: 39630268
0

LVL 81

Accepted Solution

byundt earned 2000 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

.SetRange rg
.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
``````
0

LVL 1

Author Closing Comment

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

## Featured Post

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month13 days, 6 hours left to enroll