Macro for Archiving

[afrobea_r]

Dear Excel Community,

Suppose I have a table that looks like this, whereby column B contains the months, columns C & E contains specific values. Above Column B there will be a date function that captures the dates daily.

Column B	Column C	ColumnD	Column E
Oct-17
Nov-17
Dec-17	1		1
Jan-18
Feb-18
Mar-18	2		2
Apr-18
May-18
Jun-18	3		3
Jul-18
Aug-18
Sep-18	4		4
Oct-18
Nov-18
Dec-18	5		5
Jan-19
Feb-19
Mar-19	6		6
Apr-19
May-19
Jun-19	7		7
Jul-19
Aug-19
Sep-19	8		8
Oct-19
Nov-19
Dec-19	9		9

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

What macro can I use to historically archive the data into this format at another worksheet:

	1	2	3	4	5	6	7	8	9
date
date 2

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

I will be archiving one set for data in column C and then for column E separately. Help needed! Thanks alot in advance!

Best Regards,
afrobea_r

 

[oldbrewer]

do you want Dec 17 below the 1, Mar 18 below the 2, etc

 

[afrobea_r]

do you want Dec 17 below the 1, Mar 18 below the 2, etc

Hi oldbrewer,

Thanks for your reply. For the dates, it will be a time/date stamp as I plan to archive the figures on a daily basis. This is so I can have the historical numbers to run any analyses in the future. Hope that clarifies!

 

[oldbrewer]

no, it doesn't - show us how it will look please.

 

[afrobea_r]

no, it doesn't - show us how it will look please.

Hi,

Below is an example of how it will be. Basically my intention is to curate a table of the daily prices. So if the 3rd day was 12/10/2017. I would archive it again and I now have a series.

	1	2	3	4	5	6	7	8	9
10/10/2017
11/10/2017

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

 

[oldbrewer]

but what goes in to the cell that is the intersection of 10/10/2017 and 1 ? eg where are the daily prices in your example ?

 

[afrobea_r]

but what goes in to the cell that is the intersection of 10/10/2017 and 1 ? eg where are the daily prices in your example ?

Hi,

Essentially the figures that goes into the intersection will be numerical values. The 1 to 9 is meant to indicate the 9 values that I will need on a daily basis. So if I were to include arbitrary figures...the collated table will look like this.

	1	2	3	4	5	6	7	8	9
10/10/2017	1000	5000	9000	13000	17000	21000	25000	29000	33000
11/10/2017	6000	10000	14000	18000	22000	26000	30000	34000	38000

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

 

[oldbrewer]

your original post has only months in it - now we have 9 values for each day. I must be having a bad week because I still do not understand what you want. Maybe you want the monthly total archived ?