+ Reply to Thread
Results 1 to 3 of 3

Deleting multiple rows with duplicate values in one column

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    24

    Deleting multiple rows with duplicate values in one column

    I have about 10,000 rows of information that look like this:

    Reference Date Trans Description Segment 1 Segment 2
    JE 373 3/31/13 Record deferred revenue 1030 3203
    JE 373 3/31/13 Record deferred revenue 1030 3203
    JE 373 3/31/13 Record deferred revenue 1030 3203
    JE 373 3/31/13 Record deferred revenue 1030 3203
    JE 373 3/31/13 Record deferred revenue 1030 3204
    JE 373 3/31/13 Record deferred revenue 1030 3204
    JE 373 3/31/13 Record deferred revenue 1030 3204
    JE 373 3/31/13 Record deferred revenue 1030 3205
    JE 373 3/31/13 Record deferred revenue 1030 3205
    JE 373 3/31/13 Record deferred revenue 1030 3205
    JE 373 3/31/13 Record deferred revenue 1030 3205


    and so forth, with the fifth column's (segment 2) values changing every 100 or so lines. Want to delete all the excess rows with duplicate information, but keep just one row with each of column 5's numbers. So hypothetically, I would want it to look like:


    Reference Date Trans Description Segment 1 Segment 2
    JE 373 3/31/13 Record deferred revenue 1030 3203
    JE 373 3/31/13 Record deferred revenue 1030 3204
    JE 373 3/31/13 Record deferred revenue 1030 3205
    JE 373 3/31/13 Record deferred revenue 1030 3206
    JE 373 3/31/13 Record deferred revenue 1030 3207

    etc...


    Is there a fast way to do this so I don't have to scroll through 10,000 lines of text?

    Many thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Deleting multiple rows with duplicate values in one column

    you could use =countif(F:F,F1) copied down

    Then apply filters and filter on that column for anything greater than 1...delete all those rows, remove filter
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Deleting multiple rows with duplicate values in one column

    Try this if the above doesn't work for you. You will have to paste the data somewhere else such as a new tab or next to the old data then replace. Very easy to do.

    1. Select all data
    2. Go to the Data Tab and click advanced under filter
    3. Select copy to another location
    4. Choose Location
    5. Select Unique Values
    6. Ok
    Last edited by jcranst; 07-01-2013 at 04:12 PM.

+ 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