#### Frank

##### Member

Ok I've got it now, so you do need to reset the counter for every new batch of repeats of a group. Using formulas I need 2 columns to do this, and the output would not be a contiguous list, but each total would align with the last group number in a set, see screenshot 1. Your data is highlighted red, I added more for test purposes.

That output is produced by putting a 1 in cell B1, then putting formula =IF(A2=A1,B1+1,1) in cell B2, and formula =IF(B3<B2,A2&B2,"") in cell C2. copy the formulas down cols B and C to get the results shown. Column B can be hidden, so the counts appear in the next column to A.

To give the results way you describe them (screenshot 2) requires a macro. In this layout the results bear no aligned relationship to the numbers on the left, they are a stand alone summary of the column on the left.

This macro only works for data in column A, starting in cell A1 and outputs to column B starting in cell B1.

It assumes that nothing else is in column A except that list.

**It would need to be modified if the columns were located elsewhere**.

Sub GroupRepeatCounter()

Dim i, count, c As Integer

Dim lRow As Long

lRow = Cells(Rows.count, 1).End(xlUp).Row 'get length of column A data

ReDim List(lRow) As String 'store list of repeat counts

c = 0 'array counter

For i = 0 To lRow 'initialise as empty.

List(i) = ""

Next

Columns("B:B").Select 'clear old count data

Selection.ClearContents

Range("B1").Select

count = 1 'repeat counter

Range("A1").Select

For i = 0 To lRow

If Selection.Value = Selection.Offset(1, 0).Value Then

count = count + 1

If i = 1 Then

List(c) = ActiveCell.Value & count

c = c + 1

End If

Else

List(c) = ActiveCell.Value & count

c = c + 1

count = 1

End If

ActiveCell.Offset(1, 0).Select

Next

'output array to column B

Range("B1").Select

For i = 0 To c

ActiveCell.Offset(i, 0).Value = List(i)

Next

End Sub