Removing duplicate data in a table without removing rows in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Exceluser2013

Thread Starter
Joined
Feb 7, 2013
Messages
2
I'm trying to remove duplicative data from cells in a column based on information from another cell.

Example:

Col A Col B Col C Col D
Name1 Dept 1 Salary 1 Bonus 1
Name2 Dept 2 Salary 2 Bonus 2
Name2 Dept 3 Salary 3 Bonus 2
Name3 Dept 4 Salary 4 Bonus 3

In the above, there are 2 separate entries for Name2 because they were in 2 separate depts. My source data lists the bonus amount (Bonus 2) paid to Name2 but lists it twice incorrectly and I need to remove the duplicate so it should look like this:

Col A Col B Col C Col D
Name1 Dept 1 Salary 1 Bonus 1
Name2 Dept 2 Salary 2 Bonus 2
Name2 Dept 3 Salary 3
Name3 Dept 4 Salary 4 Bonus 3

Any help or ideas would be outstanding.
 
Joined
Jun 29, 2012
Messages
518
We will need to know the Excel Version, and preferably a dummy file to work with. How does the incorrect data get in the 2nd sheet? Maybe just an incorrect formula or not the best formula for the situation. (i.e. =lookup("12345",A1:A250, B1:B250) will return an incorrect value from Col B if the actual "12345" does not exist, and the results get worse on unsorted columns)
 

Exceluser2013

Thread Starter
Joined
Feb 7, 2013
Messages
2
Version is Excel 2010. The data gets populated from transfer from our payroll system. I get it from payroll in excel form which is just the data with no formulas.

The bonus is assigned based on name and the problem I have is when an employee has multiple depts that they received pay for, it assigns the full bonus amount to each of their lines.

Currently I sort based on name and manually delete the duplicate cells, but there are typically around 1000 lines to do that for.
 
Joined
Jun 29, 2012
Messages
518
Only because Names are unreliable, I added a Number column for Employee Number.
Put in 2 Fred for Dept 1, 2 Mary for Dept 6, and 1 Mary for Dept 4.
This sorts, then removes the extra Bonus from a duplicate Employee Number.
CTRL + B will execute the macro
This work ?
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top