Compare two workbooks, two columns, return duplicates

[techgirl]

I would like to compare two columns of data - in two different workbooks to find duplicates??
Column A will have PC names and column B will have the application names, this is the same for both workbooks.

How do I compare the columns between two different workbooks, then return the duplicates to another column??

Tech girl

 

[Joe4]

Since no one has replied, I will offer this.

This easiest way is if you have Microsoft Access. You can link each list in, then do a query between the two record sets joining on both fields. It will only return the matches ("duplicates").

Otherwise you could do something like combine both fields in a formula (i.e. =A1 & B1), then do a VLOOKUP between the two lists.

 

[markmzz]

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter)

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">PCNames</td><td style="font-weight: bold;text-align: center;;">AppNames</td><td style="font-weight: bold;text-align: center;;">Duplicates</td><td style="font-weight: bold;text-align: center;;">Duplicates</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">PCName05</td><td style="text-align: center;;">AppName02</td><td style="text-align: center;;">PCName05</td><td style="text-align: center;;">AppName02</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">PCName23</td><td style="text-align: center;;">AppName11</td><td style="text-align: center;;">PCName18</td><td style="text-align: center;;">AppName12</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">PCName02</td><td style="text-align: center;;">AppName20</td><td style="text-align: center;;">PCName09</td><td style="text-align: center;;">AppName20</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">PCName24</td><td style="text-align: center;;">AppName11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">PCName23</td><td style="text-align: center;;">AppName22</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">PCName03</td><td style="text-align: center;;">AppName12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">PCName17</td><td style="text-align: center;;">AppName01</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">PCName05</td><td style="text-align: center;;">AppName06</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">PCName04</td><td style="text-align: center;;">AppName01</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">A$2:A$31,
SMALL(<font color="Green">IF(<font color="Purple">ISNUMBER(<font color="Teal">MATCH(<font color="#FF00FF">$A$2:$A$31&$B$2:$B$31,[Work02Dupl.xlsx]Sheet1!$A$2:$A$48&[Work02Dupl.xlsx]Sheet1!$B$2:$B$48,0</font>)</font>),ROW(<font color="Teal">$A$2:$A$31</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>),ROWS(<font color="Purple">C$2:C2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[techgirl]

That is great. Now if I don't want to move that data but just compare between workbooks, then highlight the duplicates, how would I do that??

 

[markmzz]

That is great. Now if I don't want to move that data but just compare between workbooks, then highlight the duplicates, how would I do that??

Maybe this:

You will need to create a Helper Column (Column C - in Work01Dupl/Sheet1) with this array formula in C2:

ISNUMBER(MATCH(A2&B2,[Work02Dupl.xlsx]Sheet1!$A$2:$A$48&[Work02Dupl.xlsx]Sheet1!$B$2:$B$48,0))


Then created a conditional formatting with this formula:

=$C2

Markmzz

 

[taurean]

With the same layout as MarkMzz suggested, you can put the following formula to use, change the RED part to suit your requirement and copy down. This is also ARRAY formula but doesn't need committing by CSE:
=IF(SUMPRODUCT(--([TheOtherBook.xlsx]Sheet1!$A$2:$A$10=$A3),--([TheOtherBook.xlsx]Sheet1!$B$2:$B$10=$B3))>0,"DUPLICATE","UNIQUE")