New Contributor

# Large/small Formula to identify the nth duplicate and return the row number

I am trying to get the specific row number of the largest or smallest value in order and unfortunately cannot seem to find a solution that gives the row number.

=MATCH(LARGE(IF(N1:N2000<>0,N1:N2000),ROW(\$1:15)),N1:N2000,0)

This is how the columns look and preferrable way to display the table for the next stage of the data conversion. Each column is a different set of data but have the same formula, bar the referenced column. As you can see highlighted are the duplicate rows where the number is the same but I need the specific row of that duplicate value.

It should be a simple fix, however I have yet to resolve it.

[data extract updated 15.09.2022 13:30 to match the attached data set]

6 Replies

# Re: Large/small Formula to identify the nth duplicate and return the row number

``=IFERROR(MATCH(LARGE(IF((N\$1:N\$2000<>0)*(COUNTIF(N\$1:N\$2000,N\$1:N\$2000)>1),ROW(\$1:\$2000)),ROW(1:1)),ROW(\$1:\$2000),0),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: Large/small Formula to identify the nth duplicate and return the row number

@Quadruple_Pawn Thank you for your response, unfortunately, the formula appears to take the values <= 2,000 from the last number in the column and work sequentially up, as opposed to in order of the greatest number.

This was the result vs expectation :-

Hopefully, this further clarifies what I am attempting to achieve.

# Re: Large/small Formula to identify the nth duplicate and return the row number

@esmiles_16

Which formula do you use in your sheet?

The suggested formula returns the expected result. I entered 11000 in cells N7 and N240 and the formula returns 240 and 7.

# Re: Large/small Formula to identify the nth duplicate and return the row number

Hello, I used the one you suggested - =IFERROR(MATCH(LARGE(IF((N\$1:N\$2000<>0)*(COUNTIF(N\$1:N\$2000,N\$1:N\$2000)>1),ROW(\$1:\$2000)),ROW(1:1)),ROW(\$1:\$2000),0),"")

# Re: Large/small Formula to identify the nth duplicate and return the row number

Can you attach your workbook without sensitive data?

# Re: Large/small Formula to identify the nth duplicate and return the row number

@Quadruple_Pawn - thank you. I was on A/L, I had to update the data set which is attached and extract in the original post.