Duplicates location in Excel Online-CodePudding

I have an online Excel workbook that I'm working on and I wanted to know if there is a way we could find out the exact location of where the text has duplicates.

I found a solution in the Excel downloaded macro workbook using VBA but I got to know Excel macro does not work online therefore I need to find a different solution to show the duplicates location in the online Excel workbook.

For instance, using conditional formatting I formatted it to show me it is a duplicate but I want it show the row number of where the duplicate is located. Does the Excel online offer any such feature?

CodePudding user response：

you can use a formula to retrieve the duplicates row:

=LET(occurence,COUNTIF($A$2:A2,$A2),
IF(occurence>=2,ROW(),""))

CodePudding user response：

=IF(COUNTIF($A$2:$A5, $A5)>1, ROW(A5), "") will return the row number as a second column

=CONCATENATE("Duplicates Exist in: ",TEXTJOIN(", ",TRUE, B2:B15)) will return those numbers back as a single cell that looks like Duplicates Exist in: 7.

Note: the above if identifies only the duplicates after the first, a slight modification would list all duplicate occurrences.

sources: https://www.ablebits.com/office-addins-blog/2016/03/02/identify-duplicates-excel/ & https://www.exceldemy.com/excel-combine-rows-into-one-cell-2/