Link to home
Start Free TrialLog in
Avatar of fireguy1125
fireguy1125

asked on

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
Avatar of byundt
byundt
Flag of United States of America image

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
Avatar of fireguy1125
fireguy1125

ASKER

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.
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.
Thanks Brad, I sent you an e-mail with the spreadsheet.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much, worked perfect!