Keeping the value of a COUNTIF after removing duplicates (Excel 2007)

Question

Hi!

I have a spreadsheet with multiple duplicate rows. I need this data sorted in order of their frequency, so I setup a COUNTIF and sorted them. Now I would like to remove the duplicate rows, but keep the COUNTIF at the same value and have Excel add to it if other duplicates are added to the spreadsheet in the future.

How can I go about doing this?

EDIT:

This may make it a little more complex, but below is an example of the data as it is now (the last number denotes the number of occurances; with my COUNTIF):

123 | Yes | 2,901 | 4,243 | 5555 | John | 4
123 | Yes | 2,919 | 4,528 | 5555 | John | 4
123 | Yes | 2,901 | 4,243 | 5555 | John | 4
123 | Yes | 4,243 | 4,128 | 5555 | John | 4
111 | 80 | 2,919 | 4,452 | 2222 | Joe | 2
111 | 80 | 11,00 | 2,901 | 2222 | Joe | 2

And this is how I need it to be:

123 | Yes | 2,901 | 4,243 | 5555 | John | 4
111 | 80 | 2,919 | 4,452 | 2222 | Joe | 2

Notice that despite some of the values not being an exact match the first cells (column A) are the same.

Hope that makes it a little more clear.

Answer 1

In case anyone is interested, below is the solution to my problem.

Sub Count()

Dim sID As String Dim sOldID As String Dim lLastRow As Long Dim lrow As Long Dim lcount As Long

lLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

lrow = 2 sID = ActiveSheet.Cells(lrow, 1).Value sOldID = ActiveSheet.Cells(1, 1).Value lcount = 1

Do While Len(sID) <> 0

If sID <> sOldID Then
    ActiveSheet.Cells(lrow - 1, 19).Value = lcount
    sOldID = sID
    lcount = 1
    lrow = lrow + 1
Else
    lcount = lcount + 1
    ActiveSheet.Rows(lrow).Select
    Selection.Delete Shift:=xlUp
End If

sID = ActiveSheet.Cells(lrow, 1).Value

Loop

ActiveSheet.Cells(lrow - 1, 19).Value = lcount

End Sub