1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Removing duplicate data in a table without removing rows in Excel

Discussion in 'Business Applications' started by Exceluser2013, Feb 7, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Exceluser2013

    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.
     
  2. 20_2_Many

    20_2_Many

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

    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.
     
  4. 20_2_Many

    20_2_Many

    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 ?
     

    Attached Files:

  5. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Was this of any help for you ?
     
  6. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088604

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice