# Build random numbers in column A

#### FryGirl

##### Well-known Member
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
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.

-----

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
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("a1:b" & r)
.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:
• FryGirl

### 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
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 #### FryGirl

##### Well-known Member
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
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.

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``````

• FryGirl

#### FryGirl

##### Well-known Member

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
@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

#### FryGirl

##### Well-known Member

Thanks for the addition Rick. This works well.

#### Rick Rothstein

##### MrExcel MVP
Thanks for the addition Rick. This works well.
You are welcome. Did you see my note about Joe's code? Do you see the same result as I posted about?

#### FryGirl

##### Well-known Member
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
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.

Replies
3
Views
174
Replies
11
Views
286
Replies
4
Views
159
Replies
9
Views
399
Replies
3
Views
276

### Forum statistics

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.

### Which adblocker are you using?    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

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