+ Reply to Thread
Results 1 to 4 of 4

Identify duplicates in spreadsheet that also have duplicate account numbers

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Annapolis, Maryland
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1

    Identify duplicates in spreadsheet that also have duplicate account numbers

    Hello,

    I have a file with 32,000+ lines. One column includes company names and another includes account numbers. The column with company names has many partial matches, such as Microsoft Corp vs. Microsoft Corp. vs. Microsoft Corporation, some with the same account numbers and some with different.

    What formula should I use to identify the unique line items based on both the company names (deduped for partial matches) and the account numbers? I believe there's something with =LEFT formula and =COUNTIF, but I couldn't figure it out.

    Thank you!

  2. #2
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    365
    Posts
    57

    Re: Identify duplicates in spreadsheet that also have duplicate account numbers

    The =Left and =Right formula's return data in a particular cell that either start from the left or right and returns a certain number or characters that you specify back to the cell the formula was written..

    Example being, if in A1 you had Microsoft Corp. In cell B2 I would do a =Left(A1,9)

    That would return only the term Microsoft since it is 9 characters long.. If your client list has similar first words you can use this method to sort.. Or just add a filter to the column and search in the filter bar for Microsoft and it will show everything that has Microsoft in the filter bar.. Press OK and there ya go.. Attach a file for us to help you better.
    Last edited by jclaborn; 11-12-2019 at 12:45 AM.

  3. #3
    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
    52,917

    Re: Identify duplicates in spreadsheet that also have duplicate account numbers

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  4. #4
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Identify duplicates in spreadsheet that also have duplicate account numbers

    The best way is upload a sample file with sample data and sample desire output.
    From your post, you want unique list, so if Microsoft is appear more than 1 time,
    Microsoft will be exclude from your list , right ?


    Regards.

+ 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. identify duplicate numbers on a row
    By pedersenn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2016, 02:45 PM
  2. How to identify duplicates between very long numbers
    By armor1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2013, 12:47 PM
  3. [SOLVED] identify duplicate numbers
    By bnwash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 02:16 AM
  4. How to identify near-duplicates based on first characters/numbers in a string?
    By archaeologista in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-06-2012, 11:21 AM
  5. Select Duplicate Account numbers and move to new sheet
    By DAKPluto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2008, 11:55 AM
  6. Deleting Duplicate Account Numbers
    By Lmsloman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2006, 02:18 PM
  7. [SOLVED] identify duplicate data in excel spreadsheet
    By Mandeep Dhami in forum Excel General
    Replies: 3
    Last Post: 07-16-2005, 09:05 AM

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