+ Reply to Thread
Results 1 to 5 of 5

Identifying Duplicates

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    9

    Identifying Duplicates

    Hello everyone,
    I have only one column with a list shown as below for example:
    There are a number of duplicate entries there. 2 Questions here:

    a. How do I count the number of Unique entries and/or duplicates?
    b. How do you display only the unique entries in the next column?

    Thanks in advance for your help.

    CANADIAN SPACE AGENCY
    BANK OF CANADA
    BELL CANADA
    POWERSTREAM INC
    ROYAL CANADIAN MINT
    IMMIGRATION AND REFUGEE BOARD OF CANADA
    CITIZENSHIP AND IMMIGRATION
    AIR CANADA
    CTC TRAIN CANADA 1075773 ONTARIO INC
    HABITAT FOR HUMANITY CANADA
    CANADIAN CENTRE FOR OHS-CCOHS OCCUPATIONAL HEALTH & SAFETY
    DEPARTMENT OF FOREIGN AFFAIRS & INTERNATIONAL TRADE
    CANADIAN SECURITY INTELLIGENCE SERVICE
    MINISTER OF NATURAL RESOURCES CANADA
    HEALTH CANADA
    CANADIAN BANK NOTE COMPANY LTD
    TREASURY BOARD OF CANADA (Hold off on this until Teresa confirms)
    SERVICE CANADA
    ELECTIONS CANADA
    NATURAL RESOURCES CANADA HUMAN RESOURCES SECTOR MSD
    MINISTRY OF NATURAL RESOURCES PROVINCIAL LOGISTICS CENTRE
    DEPARTMENT OF JUSTICE CANADA NAT ACCOM OCC H & S DIVISION
    IMMIGRATION AND REFUGEE BOARD OF CANADA
    DEPARTMENT OF JUSTICE CANADA NAT ACCOM OCC H & S DIVISION
    MINISTRY OF NATURAL RESOURCES FOREST FIRE MGMT CENTRE
    NATIONAL RESEARCH COUNCIL CANADA (Hold off on this Until Teresa confirms)
    NATIONAL GALLERY OF CANADA
    NATIONAL GALLERY OF CANADA
    MINISTRY OF NATURAL RESOURCES
    CITIZENSHIP AND IMMIGRATION
    ATOMIC ENERGY OF CANADA
    NATIONAL RESEARCH COUNCIL CANADA
    ONTARIO NORTHLAND TRANSPORTATION / ONTERA
    PUBLIC SERVICE COMMISSION OF C ANADA
    TELUS COMMUNICATIONS INC
    NATIONAL ARTS CENTRE
    NATURAL RESOURCES CANADA
    HOUSE OF COMMONS
    CANADA POST CORPORATION
    NATIONAL CAPITAL COMMISSION
    NATIONAL CAPITAL COMMISSION
    HRSDC-LABOUR CANADA/RHDCC CANADA
    CANADIAN GRAIN COMMISSION

  2. #2
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Identifying Duplicates

    You can use the following formulas to get what you need.

    Please note that these are array formulas and require Crtl Shift Enter

    Identifying Duplicates v1.xlsx

    Alternatively you can use Advanced Filter to extract unique values (see link)

    http://support.microsoft.com/kb/262277

    Let me know if this works for you
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Identifying Duplicates

    To check for duplicates you can use formula like this:

    Assuming your range is A2:A44

    =COUNTIF($A$2:A2,A2)

    To count unique records use this

    =SUMPRODUCT((A2:A44<>"")/COUNTIF(A2:A44,A2:A44&""))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Identifying Duplicates

    Assuming your list begins in col A, and each column contains a heading. Errors are trapped:

    Step 1:
    Place this into C2 to extract each unique entry from the original list:

    =IF(IFERROR(INDEX($A$2:$A$1001,MATCH(0,INDEX(COUNTIF(C$1:$C1,$A$2:$A$1001),0,0),0)),"")=0,"",INDEX($A$2:$A$1001,MATCH(0,INDEX(COUNTIF(C$1:$C1,$A$2:$A$1001),0,0),0)))

    If your list is greater than 1000 entries, please adjust the range in column A in the formula.


    Step 2:
    Place this into D2 to count the number of instances for that item found in the original list:
    =IF(C2="","",COUNTIF(A:A,C2))


    If this helps, please mark solved. . .



    Pete

  5. #5
    Registered User
    Join Date
    06-04-2014
    Posts
    9

    Re: Identifying Duplicates

    Thanks a bunch everyone for helping with this.

+ 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. Identifying Duplicates in 2 columns
    By Excel 2007 Newbie 2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2011, 10:49 PM
  2. Identifying Duplicates
    By laurance in forum Excel General
    Replies: 2
    Last Post: 05-27-2009, 06:19 AM
  3. identifying duplicates
    By sevil_gp in forum Excel General
    Replies: 2
    Last Post: 02-27-2008, 01:10 PM
  4. Identifying Duplicates
    By Daywalker in forum Excel General
    Replies: 2
    Last Post: 02-16-2007, 05:27 PM
  5. [SOLVED] Identifying and counting duplicates
    By ngarutoa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-21-2005, 08:45 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