Counting & Sorting duplicates

[sidd0053]

Hi,

Is there a way to count and sort duplicates?

I have a lot of duplicate entries in Column A, I would like to know if there is a formula, or formulas that will count the duplicates and then categorize them into a smaller list --> "X" "# of duplicates"
"Y" "# of duplicates"

I just want the Name of the duplicate once and then the number of Duplicates beside it.



I got to the point where I was able to count the duplicates, but it gave me a number beside every duplicate entry:
[TABLE="width: 651"]
<tbody>[TR]
[TD]Subject[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Classification[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Classification[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Denise Cressman[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Denise Cressman[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Leadership Plus[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

Run a pivot table.
Drop the field in both the row label and Sigma (for Count of)

 

[sidd0053]

I tried but couldn't get it to work.

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.