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
Asked
Active
Viewed 831 times
1 Answers
2
If one has the dynamic array formula TEXTJOIN and FILTER:
=TEXTJOIN(", ",TRUE,"B" & FILTER(ROW($B$2:$B$8),$B$2:$B$8=B2))
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.
Scott Craner
- 148,073
- 10
- 49
- 81
-
Sorry to ask, will it be possible,In place of cell address I can get, values in column A as comma-separated. – Nirbhay Mishra Apr 14 '21 at 15:57
-
1sure, just change the `ROW($B$2:$B$8)` to `$A$2:$A$8` and remove the `"B" & ` – Scott Craner Apr 14 '21 at 15:59