Combine lists on threes sheets with no duplication (Excell 97-2000)

[BobCrawf]

Four workbooks: January, February, March, and 1st Quarter.<br>
<br>
Sheet1 of the monthly workbooks each have a list at A1:C20<br>
A = Employees number B = Last Name C = First Name.<br>
<br>
Each monthly employee list may have the same or different employees.<br>
<br>
I need to combine the monthly lists on the quarterly worksheet and eliminate duplicates.<br>
<br>
The object is to have a quarterly list of all employees who have worked that quarter.<br>
<br>
Any Ideas? Thanks in advance for any help.<br>
<br>
Bob Crawford<br>

 

[Dreamboat]

Copy ALL the records onto a new sheet. Put column headings in row 1. Click any populated cell in column A and hit the sort button (AZ) to sort by the employee number. In D1 on the new sheet, type =a1. In D2, type =if(a2=a1,&quot;duplicate&quot;,&quot;&quot. This formula will insert the word &quot;duplicate&quot; in column D where the employee number appears is duplicated. Copy that formula down the whole length of the column (select D2 and double-click the bottom, right-hand corner of the cell). Then, sort by column D (click on any populated cell in column D and hit the sort button (AZ). Delete the entire row of all the records where the cells say &quot;duplicate&quot; in Column D. If the employee numbers are not exactly the same, you will find some duplicates so check it carefully. Good luck.

 

[BobCrawf]

Thanks for the response.<br><br>I had to get this thing out so did some candle burning and came up with the following. All is automatic (needs no user input) now.<br><br>On a blank sheet, I set up a list of 60 rows by 3 columns (Emp#, LName, FName). There are 20 rows for employees in each monthly file so that gives me room to bring everything into one list (including duplicates). <br><br>I set up a pivot table below this list using Emp# for the left and count of last names for the body. That gives me a list of all Emp# that worked that quarter with no duplicates. <br><br>On the report page I link column A to this pivot table Emp# list. Then in column B I use =Vlookup($A1,OriginalListOf60,2,false)&&quot;, &quot;&Vlookup(a1,OriginalListOf60,3,false) to pick up the names from the original list of 60. <br><br>The rest of the report then uses the Emp# in column A to pick up, calculate, and display a bunch of statistics about the employees from the monthly files. <br><br>There are some sorting and formating added to look nice but you get the idea. Vlookup is filled down column B.<br><br>The scratch page is hidden so no one sees it. It looks like magic.<br><br>Bob Crawford

 

[Dreamboat]

Way to go, Bob. I hate pivot tables and avoid them whenever possible!