+ Reply to Thread
Results 1 to 5 of 5

finding records that match a duplicate list and need to be deleted

Hybrid View

  1. #1
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    finding records that match a duplicate list and need to be deleted

    Hi.

    I have a worksheet with unique ID numbers that need to be removed because they belong in another sheet.

    I know which ID numbers need to come out, there are about 321 of them in a sheet of about 1200 records.

    The ID's are long, for example:
    94187216
    33698807
    81633181
    83132084
    32658202
    92031303
    13297016
    01245972
    Is there a way that I can search the sheet of 1200 records, find matches with the list of 321 ID numbers that need to come out, sort them to take a look and confirm before deleting them?

    Thanks for the help!

  2. #2
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    Re: finding records that match a duplicate list and need to be deleted

    Also, the file I was given has the ID numbers stored as text and there is an error box that displays anytime I click on a cell. I've tried selecting the column and changing the format to number, but nothing happens. The only way I've been able to fix it is by clicking the error message for each cell and selecting "convert to number", cell by cell. If you have any advice for fixing the formatting, that would be great! Not sure why just selecting the column and changing the format to number doesn't work. This is Excel 2011 for Mac.

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: finding records that match a duplicate list and need to be deleted

    Firstly, convert the text to numbers.
    - In a blank cell type 1
    - Copy this cell
    - Select your range of data (all the IDs)
    - Paste special - multiply

    I presume you have the list of 321 IDs that need to be deleted.

    Use in your list of 1200 IDs in a separate column
    =ISNA(MATCH(A1,Sheet2!A:A,0))
    and drag down

    then you can filter on FALSE in this column and delete those IDs

    can you work with this?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    07-24-2012
    Location
    Baltimore
    MS-Off Ver
    Excel for Mac 2011
    Posts
    45

    Re: finding records that match a duplicate list and need to be deleted

    Thanks! Both suggestions worked. I am very grateful.

  5. #5
    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,049

    Re: finding records that match a duplicate list and need to be deleted

    mistert, just fyi. changing the format of a cell, does not change the contents, only the "cosmetics" ie how it looks/appears. to change whats in the cell (text that looks like a value), you have to physically change it, using Ace_XL's suggestion.

    another way is to high-light the entire selection, scroll back to the very 1st cell, and then do what you did befor, just for the 1st cell. it will change all the high-lighted cells at the same time

    The only way I've been able to fix it is by clicking the error message for each cell and selecting "convert to number",
    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

+ 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