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.

[TABLE="width: 259"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]ColumnD[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Feb-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]May-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Feb-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]May-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sep-19[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-19[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]9[/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 651"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11/10/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 651"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD]

[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 651"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2017[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]13000[/TD]
[TD="align: right"]17000[/TD]
[TD="align: right"]21000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]29000[/TD]
[TD="align: right"]33000[/TD]
[/TR]
[TR]
[TD="align: right"]11/10/2017[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]18000[/TD]
[TD="align: right"]22000[/TD]
[TD="align: right"]26000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]34000[/TD]
[TD="align: right"]38000[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ?