+ Reply to Thread
Results 1 to 11 of 11

Count number of duplicate records.

  1. #1
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    2013
    Posts
    158

    Count number of duplicate records.

    Hello,

    My data is as follows:

    Column A
    1. Apple
    2. Banana
    3. Apple
    4. Orange
    5. Apple
    6. Banana
    7. Chickoo.

    I want a formula which gives result as 2 as only 2 items apple & banana is duplicated irrespective of their number of occurances.

    Thank you.
    Vivek.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Count number of duplicate records.

    =sum(if(frequency(match(a1:a7,a1:a7,),match(a1:a7,a1:a7,))>1,1)). Cse

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,106

    Re: Count number of duplicate records.

    =SUM(IF(FREQUENCY(IF($A$2:$A$20<>"",MATCH($A$2:$A$20,$A$2:$A$20,0)),ROW($A$2:$A$20)-ROW($A$2)+1)>1,1))
    and enter as an array - using Control+shift+enter

    ignore blanks - hence range can be more than the data you have
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,865

    Re: Count number of duplicate records.

    Or with Ctrl+Shift+Enter

    =SUM(IFERROR((COUNTIF(A1:A99,A1:A99)>1)/COUNTIF(A1:A99,A1:A99),))

  5. #5
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    2013
    Posts
    158

    Re: Count number of duplicate records.

    Thanks to all.

    Additionally, I also want to find duplicate values i.e. apple & banana in 2 cells one below the other.
    Last edited by Vivek2705; 11-29-2018 at 02:58 AM.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,106

    Re: Count number of duplicate records.

    can you give an example

  7. #7
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    2013
    Posts
    158

    Re: Count number of duplicate records.

    I want a formula in cell B1 to give apple & when I copy it down to B2 it should give banana as only these two are repeated items.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,627

    Re: Count number of duplicate records.

    Try in B1 fill down until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,106

    Re: Count number of duplicate records.

    try in b2
    =IFERROR(INDEX(A2:A15, MATCH(0, COUNTIF(B1:$B$1, A2:A15)+IF(COUNTIF(A2:A15, A2:A15)>1, 0, 1), 0)), "")

    USE control + shift + enter

  10. #10
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    2013
    Posts
    158

    Re: Count number of duplicate records.

    Solved..thanks a lot..

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,106

    Re: Count number of duplicate records.

    you are welcome

+ 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. Count only number of values within duplicate records
    By davidharper2005 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-14-2015, 03:40 AM
  2. Arrange serial number for duplicate records
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2015, 07:45 AM
  3. Count single records when duplicate records exist
    By omeng414 in forum Excel General
    Replies: 4
    Last Post: 11-27-2014, 03:42 PM
  4. Count duplicate records
    By stats09 in forum Excel General
    Replies: 9
    Last Post: 03-23-2010, 03:26 AM
  5. Count the number of records conditionally
    By leewcrawford in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-16-2008, 11:08 AM
  6. Count the number of unique records
    By joshczinger@gmail.com in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-08-2006, 03:35 AM
  7. count duplicate records in a group
    By Mparekh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2005, 10:10 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