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,

Job Standard Training	0	2
Job Standard Training	2294	2
Town hall	1550	3
Town hall	1550	3
Appeals Branch	2292	2
Appeals Conference	0	1
WR Staff Meeting	0	1
Nancy	0	2
RAD Training	0	1
TD1055 Design and Content Building	0	4
TD1055 Design and Content Building	2303	4
TD1055 Design and Content Building	2303	4
TD1055 Design and Content Building	2303	4
Presentation	0	1
T3	1949	2
ADA Forum / Nicole Blanchard	0	2

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

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.