Hi,
I am looking for a way (through VBA) to enter a sequence of numbers against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John

Try the following code:

Sub AAA()
Dim OldValue As Variant
Dim Rng As Range
Dim Ndx As Long
OldValue = Range("A1").Value
For Each Rng In Range("A1:A10")
If Rng.Value < "" Then
If Rng.Value = OldValue Then
Ndx = Ndx + 1
Rng(1, 2).Value = Ndx
Else
OldValue = Rng.Value
Ndx = 1
Rng(1, 2).Value = Ndx
End If
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of
numbers against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column
B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column
B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John

Wow, Chip that was fantastic. This is going to save a lot of time because the
dates can go down to as much as 1500 rows
Many thanks
Regards
John

"Chip Pearson" wrote:

Try the following code:

Sub AAA()
Dim OldValue As Variant
Dim Rng As Range
Dim Ndx As Long
OldValue = Range("A1").Value
For Each Rng In Range("A1:A10")
If Rng.Value < "" Then
If Rng.Value = OldValue Then
Ndx = Ndx + 1
Rng(1, 2).Value = Ndx
Else
OldValue = Rng.Value
Ndx = 1
Rng(1, 2).Value = Ndx
End If
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of
numbers against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column
B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column
B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John

Hello,

I suggest to take my UDF:

Function CountStrings(r As Range) As Variant
'Returns variant with info about strings in range r:
'First row contains count of different strings and count of empty cells

'Subsequent rows show all occurring strings (sorted) and their
frequency.
'PB V0.90
Dim k As Long
Dim lidx As Long 'index of next empty field in string table
Dim l As Long
Dim rc As Range
ReDim v(0 To r.Rows.Count * r.Columns.Count, 0 To 1) As Variant '0:
string; 1: frequency


lidx = 1
For Each rc In r
If IsEmpty(rc) Then
v(0, 1) = v(0, 1) + 1
Else
'Search for current cell value in string table
v(lidx, 0) = rc 'initialize search so that value
'will be found
l = 1
Do While v(l, 0) < v(lidx, 0)
l = l + 1
Loop
If l = lidx Then
lidx = lidx + 1 'Wasn't in. Added.
Else
If v(l, 0) < rc Then
For k = lidx - 1 To l Step -1
v(k + 1, 0) = v(k, 0)
v(k + 1, 1) = v(k, 1)
Next k
v(l, 0) = rc
v(l, 1) = 0
lidx = lidx + 1
End If
End If
v(l, 1) = v(l, 1) + 1 'increase frequency
End If
Next rc
v(lidx, 0) = ""
v(0, 0) = lidx - 1
CountStrings = v
End Function

It has been designed to count strings but it will do dates, too.

Just select a two-column range in your spreadsheet and array-enter
=CountStrings(A1:A999)
(terminate with CTRL+SHIFT+ENTER)
Change A1:A999 to your desired input area.

HTH,
Bernd

This will work on any size range:

Sub CountDates()
Dim LRow As Long, i As Long, j As Long
Dim c As Range, Lst As Range

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)

For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub

Mike F
"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of numbers
against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John

Sorry, there were 2 variables you don't need.

Sub CountDates()
Dim LRow As Long
Dim c As Range, Lst As Range

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)

For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub

Mike F

"Mike Fogleman" wrote in message
m...
This will work on any size range:

Sub CountDates()
Dim LRow As Long, i As Long, j As Long
Dim c As Range, Lst As Range

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)

For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub

Mike F
"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of numbers
against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John

Many thanks Bernd
Regards
John

" wrote:

Hello,

I suggest to take my UDF:

Function CountStrings(r As Range) As Variant
'Returns variant with info about strings in range r:
'First row contains count of different strings and count of empty cells

'Subsequent rows show all occurring strings (sorted) and their
frequency.
'PB V0.90
Dim k As Long
Dim lidx As Long 'index of next empty field in string table
Dim l As Long
Dim rc As Range
ReDim v(0 To r.Rows.Count * r.Columns.Count, 0 To 1) As Variant '0:
string; 1: frequency


lidx = 1
For Each rc In r
If IsEmpty(rc) Then
v(0, 1) = v(0, 1) + 1
Else
'Search for current cell value in string table
v(lidx, 0) = rc 'initialize search so that value
'will be found
l = 1
Do While v(l, 0) < v(lidx, 0)
l = l + 1
Loop
If l = lidx Then
lidx = lidx + 1 'Wasn't in. Added.
Else
If v(l, 0) < rc Then
For k = lidx - 1 To l Step -1
v(k + 1, 0) = v(k, 0)
v(k + 1, 1) = v(k, 1)
Next k
v(l, 0) = rc
v(l, 1) = 0
lidx = lidx + 1
End If
End If
v(l, 1) = v(l, 1) + 1 'increase frequency
End If
Next rc
v(lidx, 0) = ""
v(0, 0) = lidx - 1
CountStrings = v
End Function

It has been designed to count strings but it will do dates, too.

Just select a two-column range in your spreadsheet and array-enter
=CountStrings(A1:A999)
(terminate with CTRL+SHIFT+ENTER)
Change A1:A999 to your desired input area.

HTH,
Bernd

Many thanks Mike

In fact many thanks to all that have helped with this.
Greatly appreciated
Regards
John

"Mike Fogleman" wrote:

Sorry, there were 2 variables you don't need.

Sub CountDates()
Dim LRow As Long
Dim c As Range, Lst As Range

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)

For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub

Mike F

"Mike Fogleman" wrote in message
m...
This will work on any size range:

Sub CountDates()
Dim LRow As Long, i As Long, j As Long
Dim c As Range, Lst As Range

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Lst = Range("A1:A" & LRow)

For Each c In Lst
If c.Value = "" Then GoTo skip
c.Offset(0, 1).Value = 1
If c.Row = 1 Then GoTo skip
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1
End If
skip:
Next c
End Sub

Mike F
"JohnUK" wrote in message
...
Hi,
I am looking for a way (through VBA) to enter a sequence of numbers
against
a column of dates. For example:
If column A had 10 cells with the same date, I then want column B to
populate with numbers from 1 to 10.
If the next line had a single date, column B to show as a 1
(The dates will always be in order by the way)
If then there was a gap in column A to return a blank in column B.
To show you what I mean in a little more detail:

Date column Sequence column

Mon 17 Jul 1
Mon 17 Jul 2
Mon 17 Jul 3
Tue 18 Jul 1
Wed 19 Jul 1
Thu 20 Jul 1
Thu 20 Jul 2
Thu 20 Jul 3
Thu 20 Jul 4
Fri 21 Jul 1
Sat 22 Jul 1

Mon 24 Jul 1
Mon 24 Jul 2

I hope I explained that well enough.
Many thanks for your help
John