+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Delete duplicate rows only if they have the same value in a certain column

  1. #1
    Registered User
    Join Date
    01-17-2011
    Location
    SLC, Utah
    MS-Off Ver
    Excel 2007
    Posts
    24

    Delete duplicate rows only if they have the same value in a certain column

    I know I just asked a question... but now I am posed with a bigger one. I need to remove duplicates out of a huge file, this will take forever manually. Let me explain:


    So I have a huge column of data, the first column is the item number, second column item description, third column month formatted like this:

    Item 1 Desc 1 Month 1
    Item 2 Desc 2 Month 1
    Item 3 Desc 3 Month 1
    Item 1 Desc 1 Month 2
    Item 2 Desc 2 Month 2
    Item 3 Desc 3 Month 2

    For some reason a lot of items were input duplicated in each month like this, they aren't sorted near each other though but this is just so you can see:

    Item 1 Desc 1 Month 1
    Item 1 Desc 1 Month 1
    ...
    Item N Desc N Month 1

    Then the month changes, and the process continues. There is no pattern for duplicates from month to month, seems to be random, and there is often more than one duplicate.

    The idea is, the item numbers are obviously duplicated several times down the column because they reappear when the month column changes. I don't want to delete these duplicate rows because they hold valuable information. I'd like to delete duplicates only within the same month. Sorry if I'm not clear, I didn't make this spreadsheet so don't blame me for strange formatting! :P

    Further columns past the format I showed contain forecast data. Ideally this is exactly what I want to happen:
    Check for duplicates in item # column (only where the month column is the same)
    Delete row with the least amount of data (The entire row is not duplicated, among the duplicates some rows contain forecast data for more months, I'd like to keep the rows with the most extensive forecast data and delete the others)

    Here is the syntax im looking for, but don't know how to implement:

    if duplicate AND same value in month column AND less row data than other dupes THEN delete

    If someone could solve this for me it would be huge, otherwise I need to spend 10 hours deleting duplicates which will be EXTREMELY disheartening :p. I'll be looking at this regularly so if you need any clarification of the problem just ask.
    Last edited by derpotheman; 01-25-2011 at 11:58 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Delete duplicate rows only if they have the same value in a certain column

    You are asking this question in the Excel 2007 forum. Your profile says you have 2003.

    Decide which.

    In 2007 use Data > remove Duplicates and follow the wizard.

  3. #3
    Registered User
    Join Date
    01-17-2011
    Location
    SLC, Utah
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete duplicate rows only if they have the same value in a certain column

    I'm on 2007, must have screwed up that setting when I signed up

    I'll try your suggestion when I get to work in the morning

  4. #4
    Registered User
    Join Date
    01-17-2011
    Location
    SLC, Utah
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete duplicate rows only if they have the same value in a certain column

    So I believe this suggestion worked... but then I tried using the same function on a different sheet and here is what happened:

    http://img146.imageshack.us/img146/7...duplicates.jpg

    The first picture is just what I selected, the second picture is what happened after I clicked the first okay, and this is precisely what I wanted my data to look like. Then I click the okay that confirms what was removed, and the third picture is what I got after clicking that okay (this is not what I want)

    edit: the cells had a sumif function, after pasting values instead it worked... that was stupid!

    thanks for your help
    Last edited by derpotheman; 01-25-2011 at 11:58 AM.

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Delete duplicate rows only if they have the same value in a certain column

    I can not open the file sharing site from behind my corporate firewall. Please use the forum facilities to upload files.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1