Submit duplicates from the second occurrence

[vic7or]

Hello everbody.
I'm new using excel.
I'm trying to solve a problem with duplicate entries.
I can show what cells are duplicates, but i need display only duplicates from the second occurrence.

I'm using this Formula:

=IF(COUNT.IF($A$1:H1;H1)>1;H1;"")

It display all the occurrence that are duplicates.

I just want the second onwards.

Can you help me?

[dilipandey]

=IF(COUNT.IF($A$1:H1;H1)>1;H1;"")

It display all the occurrence that are duplicates.

How ? can you show this using an excel workbook?


Regards,
DILIPandey
<click on below * if this helps>

[ChemistB]

I'm not sure from your formula what range your total data is in? Are you looking for duplicates within a row or within a column?

[vic7or]

I solved the problem.
But i dunno why it doesn't work the first time.

I have a spreadsheet that have over 70.000 rows.
I copy the formula to all.
But this formula was displaying all the records WITH the first occurrence.
I tried again and the formula works.

Attached is an example from my spreadsheet.
Dunno why.
Why?

XD

excel_forum_example.xlsx

Here, i put the Formula to show only the unique records.

Changed =IF(COUNTIF($A$1:a1;a1)>1;a1;"")
To
=IF(COUNTIF($A$1:a1;a1)=1;a1;"DUPLICATED")