+ Reply to Thread
Results 1 to 6 of 6

Removing an entire line if there is a duplicate

  1. #1
    Registered User
    Join Date
    06-07-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    36

    Removing an entire line if there is a duplicate

    Hi -I am trying to purge duplicates out of my excel spreadsheet. I know there is a function where you hit "remove duplicates" but that only removes the duplicate from that column which then screws up the other fields.
    So basically I have 12 columns... if there is a duplicate email address in Column C, I want to remove that entire ROW, not just the email address. Any thoughts?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,858

    Re: Removing an entire line if there is a duplicate

    you could use a formula like this to tag the duplicates =COUNTIF($A$2:A2,A2) and drag down. This will give a 1 to originals and a 2 to duplicates. Then sort on the column ascending (or descending) with the formula (copy and paste special values first) then you'll have all the duplicates together (2s and above). Then just highlight the entire rows they are in and hit delete.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Removing an entire line if there is a duplicate

    Two ways to do it.
    Use VBA - sorry, I can't help with this.
    Use a Sort/Filter/Re-sort.

    1. Add another column - label it 'Sort'.
    2. In the first data row of that new column, enter the number 1.
    3. Continue down the columns: 1,2,3,4...
    Once you've done the first 1,2 you should be able to auto-fill down the rest of the rows.
    4. Select your header row.
    5. On the Data tab, click the Filter button, which will put sort/filter arrows in each cell of your header row.
    6. Click the arrow for your e-mail address column and select Sort A to Z.
    7. Your data will now be sorted with the duplicate e-mail addresses grouped together.
    8. Delete the duplicate rows.
    9. Now go to the new Sortcolumn you inserted, click the arrow and select Sort Smallest to Largest. This will put your data back in its original order.

    If you have so many rows that seeing and/or deleting the duplicates manually (step 8) will be difficult / very time-consuming, try this:
    8a. Select the e-mail address column (I'm going to assume it's column A - change the references below as needed) - so A2:A1000 or whatever.
    8b. Go to Conditional FormattingNew RuleUse a formula to determine which cells to format.
    8c. Enter this formula:
    =A2=A1
    8d. Choose a fill colour which you don't use for anything else. Click OK.
    8e. Click the sort/filter arrow on your e-mail address column and select Filter by color. Choose the colour you selected in 8d above.
    8f. You now have only the duplicate rows shown.
    8g. Select the data you can see (but not the header row).
    8g. Press Alt-; (Alt and semi-colon together). This will select only the visible cells in the selection.
    8h. Delete those rows.
    8i. Click the arrow on your e-mail column header and select Clear filter from label.

    That should do it.
    If you're not sure about the instructions above, try on a back-up copy first (always a good idea anyway).

    Hope that helps - sorry it's a bit long!
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,889

    Re: Removing an entire line if there is a duplicate

    I think you are mistaken. Remove Duplicates will remove entire rows. By default, when you select Remove Duplicates the whole data range will be selected and you will be give the option to choose which column(s) to test for duplicates.

    That said, I don't think Remove Duplicates was available in Excel 2003, as per your profile.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    06-07-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Removing an entire line if there is a duplicate

    This was perfect. Simple and effective. Thanks!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,889

    Re: Removing an entire line if there is a duplicate

    Which answer are you referring to?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Removing duplicate text in a single line
    By bwallan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-14-2015, 10:32 AM
  2. Macros to delete entire duplicate row for duplicate values
    By cutelebel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2014, 12:09 PM
  3. [SOLVED] Removing Trailing Spaces from Cells in Entire Spreadsheets
    By WarMachine in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2013, 10:08 AM
  4. Removing Entire Rows based on a Value in a Column
    By Tschmidt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2013, 03:00 PM
  5. Replies: 3
    Last Post: 03-27-2013, 12:37 PM
  6. Replies: 4
    Last Post: 09-18-2012, 09:06 AM
  7. Search column a for duplicate data and copy entire row to duplicate sheet
    By crazyAMP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2012, 08:21 PM

Tags for this Thread

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