need formula, for counting and deleting duplicate entries

[sidd0053]

I want excel to automatically count a duplicate, show the value (number of duplicates) in another cell and then delete the duplicate while still maintaining that value.

Basically the algorithm would be:
1.Excel sees there's a duplicate.
2.Plus 1 to the column B of the first occurrence.
3.Delete the duplicate while still maintaining the value of the B column in the first occurrence.
4.Repeat the process if there is more duplicate entry.

 

[bhos123]

suppose you have data in column A starting from A2, enter the formula '=IFERROR(MATCH(A2,$A$1:A1,0),0)' in B2 and drag down. filter all the non-zeros in column B, and right click and then select 'Delete row' option.

 

[sidd0053]

suppose you have data in column A starting from A2, enter the formula '=IFERROR(MATCH(A2,$A$1:A1,0),0)' in B2 and drag down. filter all the non-zeros in column B, and right click and then select 'Delete row' option.

It didn't really work out,

[TABLE="width: 789"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Job Standard Training[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Job Standard Training[/TD]
[TD="align: right"]2294[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Town hall[/TD]
[TD="align: right"]1550[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Town hall[/TD]
[TD="align: right"]1550[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Appeals Branch[/TD]
[TD="align: right"]2292[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Appeals Conference[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]WR Staff Meeting[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Nancy[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]RAD Training[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TD1055 Design and Content Building[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TD1055 Design and Content Building[/TD]
[TD="align: right"]2303[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TD1055 Design and Content Building[/TD]
[TD="align: right"]2303[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TD1055 Design and Content Building[/TD]
[TD="align: right"]2303[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Presentation[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]T3[/TD]
[TD="align: right"]1949[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ADA Forum / Nicole Blanchard[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Column B is the formula that you gave, Column C is how many duplicates there are with the formula that I used. Except with my formula if I delete the duplicate entries in column 'A' than the # shown in 'C' changes accordingly. TD1055 DESIGN is showing 2303, its not possible that there are that many entries for it.

 

[Marcelo Branco]

Say your data are in A2:A100

Steps
Formula in B2 copied down until B100
=COUNTIF(A:A,A2)-1

Select B2:B1100
copy and paste only values

Select A2:B100
Data > Remove duplicates

M.

 

[sidd0053]

Say your data are in A2:A100

Steps
Formula in B2 copied down until B100
=COUNTIF(A:A,A2)-1

Select B2:B1100
copy and paste only values

Select A2:B100
Data > Remove duplicates

M.

This works for me, thank you.

Just wondering as to why the "-1" in the formula?

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

About -1
It is not necessary - thought you wanted to count only when there was a duplicate

M.