×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

(OP)
Four workbooks: January, February, March, and 1st Quarter.

Sheet1 of the monthly workbooks each have a list at A1:C20
A = Employees number B = Last Name C = First Name.

Each monthly employee list may have the same or different employees.

I need to combine the monthly lists on the quarterly worksheet and eliminate duplicates.

The object is to have a quarterly list of all employees who have worked that quarter.

Any Ideas? Thanks in advance for any help.

Bob Crawford

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

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,"duplicate",""). This formula will insert the word "duplicate" 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 "duplicate" in Column D. If the employee numbers are not exactly the same, you will find some duplicates so check it carefully. Good luck.

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

(OP)
Thanks for the response.

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.

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).

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.

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)&", "&Vlookup(a1,OriginalListOf60,3,false) to pick up the names from the original list of 60.

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.

There are some sorting and formating added to look nice but you get the idea. Vlookup is filled down column B.

The scratch page is hidden so no one sees it. It looks like magic.

Bob Crawford

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!