Frank
Member
Hi C Drake,
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
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
