Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jul 05, 2022
Solved

Count Duplicate with the help of Excel Functions

Hello Everyone, 

 

I have two questions :

1 -- Tells how many rows/columns have duplicates with the help of formula? E.g. 2 rows and 3 columns have duplicate values.

 

2 --  Tells the specific rows/columns with duplicate values with the help of formula ? E.g. Hire Date has duplicate values.

 

 

Please help..

 

Here is a attached file.. 

 

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jul 05, 2022

    Excel 

    As Riny_van_Eekelen points out, the question does not make much sense in the context of the given dataset.  The possible solutions also depend on the version of Excel you have.  For example, using Excel 365 one could define a Lambda function

    = LAMBDA(range,
    LET(
        distinct, UNIQUE(range),
        duplicated?, COUNTIFS(range, distinct) > 1,
        SUM(N(duplicated?))
    )
)

    so that applying it column by column

    = BYCOL(Table1, CountDupsĪ»)

    gives

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 05, 2022

    Excel 

    As Riny_van_Eekelen points out, the question does not make much sense in the context of the given dataset.  The possible solutions also depend on the version of Excel you have.  For example, using Excel 365 one could define a Lambda function

    = LAMBDA(range,
    LET(
        distinct, UNIQUE(range),
        duplicated?, COUNTIFS(range, distinct) > 1,
        SUM(N(duplicated?))
    )
)

    so that applying it column by column

    = BYCOL(Table1, CountDupsĪ»)

    gives

     

    • Excel's avatar
      Excel
      Iron Contributor
      Jul 05, 2022
      Thank you so much sir.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 05, 2022

    Excel I pulled your data into Power Query and found that four sets of rows are duplicated. That is, where all of the fields are the same. And because there are duplicate rows, all 8 columns contain duplicates.

     

    But perhaps I misunderstood your questions. Then you can perhaps clarify your intentions and identify and count the duplicates manually, explaining the logic.

Resources