+ Reply to Thread
Results 1 to 8 of 8

I need to delete duplicate values without losing/eliminating the rows they are in.

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    I need to delete duplicate values without losing/eliminating the rows they are in.

    Hello-

    I am working on a huge spreadsheet for work and I have multiple values in a column, like so:

    AAA
    BBB
    BBB
    BBB
    CCC
    DDD
    DDD

    I need it to read:
    AAA
    BBB


    CCC
    DDD


    (In other words, I still need the rows intact, just the duplicate values eliminated).

    Help please?

    Thanks!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    One way


    Please Login or Register  to view this content.
    Note: Assuming row 1 is a header row
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    04-13-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    Depending on how many values, you could just filter it and then delete everything but the first one
    For example, filter so you see:
    AAA
    AAA
    AAA

    Then just delete everything past the first AAA. Now filter by BBB only, etc. Not a great fix but would work if you don't want to use VBA and have few values

  4. #4
    Registered User
    Join Date
    08-22-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    Hey all-thanks for the tips. I actually realized this morning that I could use a simple function to get it done:

    =IF(A2=A1,"",A2), in column B

    This gets rid of all the duplicates by returning a blank cell.

    Thanks!

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    Just realized that my method wasn't entirely effective, as it only worked for consecutive duplicate values. I also have duplicates like this:

    AAA
    BBB
    BBB
    BBB
    CC
    CC
    AAA
    DDD

    In that case, I'd need to eliminate the AAA's and the BBB's and CC's.
    Also, I have realized that I don't actually remember how to implement the solution you suggested, Jeff! I'm pretty rusty on the Macro front. I'd appreciate help.

    Thanks!

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    Try this...

    ...but can you not sort the column first. This macro will not work on an unsorted list.

    Highlight macro to copy >> Ctrl + C >> Open your workbook
    Alt + F11 >> opens the Visual Basic Editor (VBE)
    Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
    Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
    Paste code >> Ctrl + V (right side of screen)
    Alt + Q >> exits VBE and returns to Excel
    Back in Excel to run the macro >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

    Where to paste code

  8. #8
    Registered User
    Join Date
    08-22-2012
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need to delete duplicate values without losing/eliminating the rows they are in.

    Thanks Jeff! Worked like a charm.

    *SOLVED

+ 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