How can I get cell address with duplicate values

user_1463552 0
Viewed 402

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

data-analysis excel-formula excel
1 Answers

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.

user_48515900
Related