CODEWITHSUNDEEP
excelexcel-formuladata-analysis
Nirbhay Mishra
Nirbhay Mishra

Reputation: 1648

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

Upvotes: 1

Views: 1415

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

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.

Upvotes: 2

Related Questions