Duplicate Rows returning the row number for each

[skelly90]

Hi,

As per the title I would like a formula to find and return each instance of a duplicate line's row number. Then a lookup function using the row number to take the information from one sheet to another.


Notes: There may be more than 2 lines of dupicate data,

The duplicate data will with in this range A5:A131,


Hope this makes sense, if you need any more information, let me know.

 

[SirJB7]

Hi, skelly90!


Tried using CF? Use this formula for A5 cell and copy format down thru A131:

=COUNTIF(A$5:A$131,A5)>1


Regards!

 

[skelly90]

Thanks, I have tried it, but it doesn't return the row number the dupicate(s) are on, could I combine the countif with an IF to return the row number if its true?

 

[Luke M]

Generate list of row numbers with duplicates:

=SMALL(IF(COUNTIF($A$5:$A$131,$A$5:$A$131>1,ROW($A$5:$A$131)),ROW(A1))

Confirm formula using Ctrl+Shift+Enter, then copy down as needed. To get the values in those rows, would just do:

=INDEX(A:A,B2)

 

[SirJB7]

Hi, skelly90!


You're right, that doesn't retrieve the duplicates row numbers, just lets you highlight/format the duplicates. Another approach than Luke M's might be using a helper column, say B, and type in B5 and copy down:

=SI(CONTAR.SI(A$5:A$131;A5)>1;FILA();"") -----> in english: =IF(COUNTIF(A$5:A$131,A5)>1,ROW(),"")


Regards!

 

[skelly90]

Thank you both for the answers, couldn't quite get Luke M's to work, but have saved it so I can try again once I have some free time.

Finally got a decent solution to make much easier for me to use the spreadsheet.


Much Appreciated

Ryan

 

[SirJB7]

Hi, skelly!

Glad you could solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!