Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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

Sequence of numbers
 

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