Search code examples
excelexcel-formuladata-analysis

How can I get cell address with duplicate values

In a column, there are multiple cells with duplicate values. How can I get cell address with duplicate values?

For example in the sample below Apple is repeated in B2, B5, B7

enter image description here

Solution

  • If one has the dynamic array formula TEXTJOIN and FILTER:

    =TEXTJOIN(", ",TRUE,"B" & FILTER(ROW($B$2:$B$8),$B$2:$B$8=B2))

    enter image description here

    Since there is a version that has TEXTJOIN and not FILTER:

    =TEXTJOIN(", ",TRUE,"B" & IF($B$2:$B$8=B2,ROW($B$2:$B$8),""))

    This would only work in Excel 2019 and will require the use of Ctrl-Shift-Enter to force an array formula.

    If one does not have TEXTJOIN look HERE for a UDF that mimics TEXTJOIN and use the second formula with Ctrl-Shift-Enter.