Build random numbers in column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
Hello all,

In column A starting with A2, I would like to fill down with 1, 2, 3, 4, 5, 6, etc., but I would like to get random numbers between 3 and 8.

So, for example, see below. I will then fill in column B with dates. In the end, I will drag this down as far as today's date.

Budget.xlsm
AB
1NumbersDate
211-Jan-20
311-Jan-20
411-Jan-20
511-Jan-20
611-Jan-20
722-Jan-20
822-Jan-20
922-Jan-20
1022-Jan-20
1122-Jan-20
1222-Jan-20
1322-Jan-20
1422-Jan-20
1533-Jan-20
1633-Jan-20
1733-Jan-20
1844-Jan-20
1944-Jan-20
2044-Jan-20
2144-Jan-20
Sheet4
Cell Formulas
RangeFormula
B7:B21B7=IF(A6<>A7,MAX($B$6:B6)+1,B6)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
If everything was equal at 8 rows, then I would end up with 2,920 rows.
I think you might want 2928 rows though (allowing for leap years).

I believe that would be 2922 rows: 3 sets of 365 plus 1 set of 366, repeated twice (8 rows total).

But I don't believe we know (not I, at least) the relationship between the random sequences of 1 to 365 (366?) and the dates next to them.

So we don't know if 366 would, in fact, be valid. What say you, FryGirl?

If VBA would be an acceptable method of generating the data, try the macro below.

Alternatively, download the Excel file (click here). Ignore any preview errors or request to login. Just proceed to download.

-----

Note.... I wrote the macro before the latest exchange between you and Jason. I don't know how my VBA code compares with what Jason provided, which you like.

Also, you originally said you want the date sequence to end with today's date. That is what the macro does.

But now you say you like having a __start__ date in D2, as Jason provided. Please clarify that requirement.

FYI, I consider it "evil" to set Iterative calculation mode. At the very least, be sure to disable it again after using Jason's method. My method does not rely on it, of course.

As a proof of concept, I would include the following in Excel.

rand 1 to 365.xlsm
DEFGHI
2freqfreq
3141999#rows352
4283min471
5378max561
647665
756764
867852
9751999
Sheet2


Select cells or hover cursor over cells to see formulas. Note that the sequence 1 to 365 in column E extends to E367.

-----

VBA Code:
Sub doit()
' I would like [... a] list numbers down column A (1, 2, 3, 4, 5, 6, …365),
' but each one of those numbers (1, 2, 3, 4, 5, 6, …365)
' will only be listed randomly between 3 to 8 times.
' If everything was equal at 8 rows, then I would end up with 2,920 rows
'  I will then fill in column B with dates [... down to] today's date
Dim i As Long, j As Long, n As Long, r As Long, d As Long

' generate random 1 to 365
Application.ScreenUpdating = False
Columns("a:b").Clear
ReDim v(1 To 365 * 8, 1 To 2) As Variant
For i = 1 To 365
    n = WorksheetFunction.RandBetween(3, 8)
    For j = 1 To n
        r = r + 1
        v(r, 1) = i
        v(r, 2) = "=RAND()"
    Next
Next
Range("a1:b" & r) = v
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("b1:b" & r), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("a1:b" & r)
    .Header = xlGuess
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

' generate dates
d = Date - r + 1
ReDim v(1 To r, 1 To 1) As Variant
For i = 1 To r: v(i, 1) = d: d = d + 1: Next
With Range("b1:b" & r)
    .Clear
    .Value = v
    .NumberFormat = "d-mmm-yyyy"
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
MsgBox "done"
End Sub
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
I believe that would be 2922 rows: 3 sets of 365 plus 1 set of 366, repeated twice (8 rows total).
From 1 Jan to 31 Dec (inclusive) in a leap year is 366 days. With the max 8 rows per day, 8*366 = 2928. Reading your reply, I believe that you have interpreted the question as random days rather than a random number of rows per day.
Also, you originally said you want the date sequence to end with today's date.
Yeah, I kinda got carried away and forgot that bit :oops:
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
In this case, 366 is not all that necessary nor is really up to this date. I just need to generate the sequenced numbers in column A with the dates in column B for a year's worth. I tried to modify the code, but don't know how to generate a date after the first date. I hardcoded a date into the code (1-Jan-20), and all the 1's get this date, but starting with 2, the dates should be 2-Jan-20, with the 3's, 3-Jan-20 and so on. Can you help me with that part?

VBA Code:
Sub doit()
' I would like [... a] list numbers down column A (1, 2, 3, 4, 5, 6, …365),
' but each one of those numbers (1, 2, 3, 4, 5, 6, …365)
' will only be listed randomly between 3 to 8 times.
' If everything was equal at 8 rows, then I would end up with 2,920 rows
'  I will then fill in column B with dates [... down to] today's date
Dim i As Long, j As Long, n As Long, r As Long, d As Long

' generate random 1 to 365
Application.ScreenUpdating = False
Columns("a:b").Clear
ReDim v(1 To 365 * 8, 1 To 2) As Variant
For i = 1 To 365
    n = WorksheetFunction.RandBetween(3, 8)
    For j = 1 To n
        r = r + 1
        v(r, 1) = i
'        v(r, 2) = "=RAND()"
    Next
Next

Range("a1:a" & r) = v

For i = 1 To r
    If Range("A" & i).Value2 = 1 Then Range("B" & i).Value2 = "1-Jan-20"
Next i
    
'' generate dates
'd = Date - r + 1
'ReDim v(1 To r, 1 To 1) As Variant
'For i = 1 To r: v(i, 1) = d: d = d + 1: Next
'With Range("b1:b" & r)
'    .Clear
'    .Value = v
'    .NumberFormat = "d-mmm-yyyy"
'End With
Application.ScreenUpdating = True
MsgBox "done"
End Sub
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
Note that the sequence 1 to 365 in column E extends to E367.

Typo: I meant "column D" and "to D367".


From 1 Jan to 31 Dec (inclusive) in a leap year is 366 days. With the max 8 rows per day, 8*366 = 2928.

Based on FryGirl's clarification, I concur. The dates cover only one "year" (12 months), not up to 1095 to 2922 days, as I had (mis)interpretered.


I believe that you have interpreted the question as random days rather than a random number of rows per day.

Actually, I (mis)interpreted FryGirl's requirement to be __both__: a random number (3 to 8) of each of 365 days in random order.


366 is not all that necessary

Odd that you say that. Jason is correct: if you want the column of dates to encompass an entire "year" (12 months), we should allow for up to 366 day numbers.

The macro below does that.


I hardcoded a date into the code (1-Jan-20), and all the 1's get this date, but starting with 2, the dates should be 2-Jan-20, with the 3's, 3-Jan-20 and so on.

Try the macro below. Alternatively, you can re-upload the Excel file (click here). Same link; new contents.

The macro assumes a start date in B2. It does not have to 1-Jan.

Again, as a check, I suggest the following additional Excel formulas, just for initial testing purposes.

rand 1 to 365.xlsm
DEFGHIJ
1day#freq#dupesfreq
2143min freq353
3288max freq459
4352063#dates561
543366max day#662
658757
767874
8762063#dates
Sheet3


Select cells or hover cursor over cells to see formulas. The sequence 1 to 366 in column D extends to D367.

-----

Excel Formula:
Option Explicit

'***************************************************
'*** enter a start date into B2 before executing ***
'***************************************************

Sub doit()
Dim v(1 To 2928, 1 To 2) As Double
Dim curDate As Double
Dim dateFormat As String
Dim i As Long, j As Long, r As Long
Dim nDays As Long, nRows As Long

curDate = Range("b2").Value2
dateFormat = Range("b2").NumberFormat
nDays = WorksheetFunction.EDate(curDate, 12) - curDate
For i = 1 To nDays
    nRows = WorksheetFunction.RandBetween(3, 8)
    For j = 1 To nRows
        r = r + 1
        v(r, 1) = i
        v(r, 2) = curDate
    Next
    curDate = curDate + 1
Next
Range("a2:b2929").Clear
Range("a2:b" & r + 1).Value2 = v
With Range("b2:b" & r + 1)
    .NumberFormat = dateFormat
    .EntireColumn.AutoFit
End With
End Sub
 
Solution

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This does it perfectly Joe. Thank you for your time.

This is what I was able to cobble together, but of course, neat seeing the way you have done it.

VBA Code:
Sub doit()
    Dim i As Long, j As Long, n As Long, r As Long, d As Long, lr As Long
    Application.ScreenUpdating = False
    Columns("A:B").Clear
    ReDim v(1 To 365 * 8, 1 To 2) As Variant
    For i = 1 To 365
        n = WorksheetFunction.RandBetween(3, 8)
        For j = 1 To n
            r = r + 1
            v(r, 1) = i
        Next
    Next
    Range("A2:A" & r) = v
    For i = 2 To r
        If Range("A" & i).Value2 = 1 Then Range("B" & i).Value2 = "1-Jan-20"
    Next i
    lr = Range("B" & Rows.Count).End(xlUp).Row + 1
    With Range(Cells(lr, 2), Cells(r, 2))
        .FormulaR1C1 = "=IF(R[-1]C[-1]<>RC[-1],MAX(R[-1]C2:R3C)+1,R[-1]C)"
        .NumberFormat = "d-mmm-yy"
        .Value2 = .Value2
    End With
    Application.ScreenUpdating = True
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
@joeu2004 - When I run your code starting at January 1, 2020, it stops at December 30th, not December 31st.

@FryGirl - Give this at try... assuming A1 and B1 are headers, put your starting date in cell B2 and then run this macro...
VBA Code:
Sub RandomDateRepeats()
  Dim I As Long, N As Long, X As Long, D As Date, Arr(1 To 2299, 1 To 2) As String
  D = [B2]
  Do While D <= DateSerial(Year([B2]), 12, 31)
    For X = 1 To [RANDBETWEEN(3,8)]
      N = N + 1
      Arr(N, 1) = D - CDate([B2]) + 1
      Arr(N, 2) = D
    Next
    D = D + 1
  Loop
  Application.ScreenUpdating = False
  [A2:B2299] = Arr
  [B2:B2299].Value = [B2:B2299].Value
  Application.ScreenUpdating = True
End Sub

EDIT NOTE: The code was modified from what I posted originally.
 
Last edited:

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for the addition Rick. This works well.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
Hi Rick,

No, I did not see the same results. The code Joe posted in #14 fills all the way down to 31-Dec-20
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
Hi Rick,

No, I did not see the same results. The code Joe posted in #14 fills all the way down to 31-Dec-20
Strange... I don't see it anymore either... it now goes down to December 31st like it should. I must have done something wrong originally but I cannot imagine what. Anyway, sorry for the alarm Joe... your code is working fine for me now.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,846
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top