How to insert 20 dates for each cell in a column

Question

I have been given an excel column with numbers and I want to create a new column with every cell content repeating 20 times and 20 dates sequentially next to it to another column.
How can I achieve this in excel or access?
For example I have a column with numbers as follows:

0491703316
0235361458
0380458968
0240510098
0169346827
0147826672

and I want to do this

0491703316 01/12/2016
0491703316 02/12/2016
0491703316 03/12/2016
0491703316 04/12/2016
......................
......................
0235361458 01/12/2016
0235361458 02/12/2016
0235361458 03/12/2016
0235361458 04/12/2016
........................
........................
........................
0380458968
0240510098
0169346827
0147826672

score 1 · Accepted Answer · answered Apr 24 '17 at 13:11

1

Link the range in Access as a linked table named, say, LinkedTable.

Then create this query:

PARAMETERS 
    Period Text ( 255 ), 
    Periods Short, 
    FirstDate DateTime;
SELECT DISTINCT
    LinkedTable.Number, 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1 As Sequence,
    DateAdd([Period],[Sequence]-1,[FirstDate]) AS [DateStart]
FROM 
    LinkedTable,
    MSysObjects AS Uno, 
    MSysObjects AS Deca
WHERE 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<[Periods]

Run this with the parameters:

Period: d
Periods: 20
FirstDate: 2016-12-01

answered Apr 24 '17 at 13:11

Gustav

53,498
7
29
55

Adopted from: [Create a date table between 2 dates](https://stackoverflow.com/questions/43311939/create-a-date-table-between-2-dates/43316721#43316721). – Gustav Apr 24 '17 at 13:12
There is a problem though. The sequence goes 1, 2, 3 and then jumps to 11, 12.... up to 20. Same for dates. So it's missing 4 to 10! – tkyo Apr 25 '17 at 09:26
That is weird. Which Access version? – Gustav Apr 25 '17 at 10:15
Access 2013 under windows XP – tkyo Apr 25 '17 at 10:20
Are you sure? Not even Vista is supported for Access 2013. – Gustav Apr 25 '17 at 10:28
sorry windows 7 wanted to say. – tkyo Apr 25 '17 at 11:13
1

Found out. You have removed the `PARAMETERS` section, right? – Gustav Apr 25 '17 at 12:32

score 0 · Answer 2 · answered Apr 24 '17 at 11:40

0

Assuming 0491703316 is in A1, in B8 enter 01/12/2016 and copy down to B28. Copy B8:B28 to B29. Select B8:B29 and drag down to B133. In A8 enter =A1 and in A9:

=IF(B9=B$8,INDEX(A:A,MATCH(A8,A:A,0)+1),A8)

Copy A9 down to suit.

answered Apr 24 '17 at 11:40

pnuts

58,317
11
87
139

How to insert 20 dates for each cell in a column

2 Answers2