Comparing lists

[jackfox68]

Is there a way to compare what is suppose to be two identical lists of employees and show what the discrepancies are using excel? I have one list I receive from my HR department and my WFM team maintains another list and there always seems to be discrepancies, just trying to find a more efficient way of pointing them out. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks <o></o>
Brian <o></o>

 

[Dannyh1]

Hi,

Use a vlookup

 

[jackfox68]

Tahnsk Danny! Can you elaborate on how I would use it to compare the whole list? I don't think I understand how the V-Lookup would povide a solution. Still a bit noobish when it comes to using v-lookup.

 

[jackfox68]

I believe I found a solution with the information you provided. Will keep you posted.

Thanks again for your response, it is greatley appreciated!

Brian

 

[Matty]

One way...

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:71px;"><col style="width:19px;"><col style="width:150px;"><col style="width:150px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>HR List</td><td>WFM List</td><td>
</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">Matty</td><td style="text-align:right; ">Matty</td><td>
</td><td>WFM List not in HR List</td><td>HR List not in WFM List</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">John</td><td style="text-align:right; ">John</td><td>
</td><td style="text-align:right; ">Jim</td><td style="text-align:right; ">Terry</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">Paul</td><td style="text-align:right; ">Paul</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">Terry</td><td style="text-align:right; ">Jim</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; ">Frank</td><td style="text-align:right; ">Frank</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
HR List in column A; WFM list in column B.

Formulas used:

D1:

=SUMPRODUCT(--ISNA(MATCH(B2:B6,A2:A6,0)))

D3:

=IF(ROWS(D$3:D3)<=D$1,INDEX(B$2:B$6,SMALL(IF(ISNA(MATCH(B$2:B$6,A$2:A$6,0)),ROW(B$2:B$6)-ROW(B$2)+1),ROWS(D$3:D3))),"")

E1:

=SUMPRODUCT(--ISNA(MATCH(A2:A6,B2:B6,0)))

E3:

=IF(ROWS(E$3:E3)<=E$1,INDEX(A$2:A$6,SMALL(IF(ISNA(MATCH(A$2:A$6,B$2:B$6,0)),ROW(A$2:A$6)-ROW(A$2)+1),ROWS(E$3:E3))),"")

The formulas in D3 and E3 need committing with CTRL+SHIFT+ENTER and can then be copied down.

Matty

 

[jackfox68]

Danny - your suggestion worked for what I needed. Matty I think I will be able to use what you provided me to deliver an even more useful tool

Thanks to both of you!

Can't wait until my excel skills are as good as yours!

Brian

 

[Dannyh1]

sorry for late reply - got caught up yesterday - glad you're sorted