Help for Charting of weekly data to daily data

[Access Beginner]

Hi guys,

I have a data set ( screen shot below. 1st time using Jeanie HTML, so I hope this works).

I have only import two weeks of data, but this data will be for at least 12 months.

Data as extracted

Excel Workbook
	C	D	E	F	G	H	I	J
1	Data as extracted
2		Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	29/04/2011	738	289	25	343	536	582	938
4	22/04/2011	653	816	173	507	367	942	154
Sheet1

Desired Results

Excel Workbook
	C	D
6	Deisred Result
7	Friday, 29 April 2011	938
8	Thursday, 28 April 2011	582
9	Wednesday, 27 April 2011	536
10	Tuesday, 26 April 2011	343
11	Monday, 25 April 2011	25
12	Sunday, 24 April 2011	289
13	Saturday, 23 April 2011	738
14	Friday, 22 April 2011	154
15	Thursday, 21 April 2011	942
16	Wednesday, 20 April 2011	367
17	Tuesday, 19 April 2011	507
18	Monday, 18 April 2011	173
19	Sunday, 17 April 2011	816
20	Saturday, 16 April 2011	653
Sheet1

The data as extracted is good for weekly analysis, but I do not know how to convert it two daily data, except manually. Not a big deal for a small data set, but I have 12 months of data in this format.

Hoping someone can be of assistance.

 

[martindwilson]

in the same sheet for easiness
in k1 put "new list .in k2 =c3 then k3=k2-1 drag down, format cells custom
ddd, dd mmm yyyy
in L1 put say "values" in L2 put
=INDEX($D$3:$J$200,CEILING(ROW(C1)/7,1),7-MOD(ROW(C7),7))
drag down to last date in col K (increase $J$200 to however big your data set is)

 

[Access Beginner]

Thanks martindwilson,

Worked like a charm!

If you dont mind, can you please explain how this formual works? I've never seen CEILING before in a formula.


******** type=text/javascript> vbmenu_register("postmenu_2702684", true); *********>

 

[Access Beginner]

sorry if you could also if possible show me how to using the same data source, have the dates listed in 'new list' as oldest to newest not newest to oldest, cant figure out how to change the formula to pick up these dates as it gives same values as the first list as it doesnt actually use dates in formula.

 

[Haseeb Avarakkan]

Hello, Try

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Total Days</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">Fri, April 29, 2011</td><td style="text-align: right;;">938</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">Thu, April 28, 2011</td><td style="text-align: right;;">582</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">Wed, April 27, 2011</td><td style="text-align: right;;">536</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">Tue, April 26, 2011</td><td style="text-align: right;;">343</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">Mon, April 25, 2011</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">Sun, April 24, 2011</td><td style="text-align: right;;">289</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">Sat, April 23, 2011</td><td style="text-align: right;;">738</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">Fri, April 22, 2011</td><td style="text-align: right;;">154</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">Thu, April 21, 2011</td><td style="text-align: right;;">942</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">Wed, April 20, 2011</td><td style="text-align: right;;">367</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">Tue, April 19, 2011</td><td style="text-align: right;;">507</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">Mon, April 18, 2011</td><td style="text-align: right;;">173</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">Sun, April 17, 2011</td><td style="text-align: right;;">816</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">Sat, April 16, 2011</td><td style="text-align: right;;">653</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M1</th><td style="text-align:left">=COUNT(<font color="Blue">C:C</font>)*7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L3</th><td style="text-align:left">=MAX(<font color="Blue">C:C</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M3</th><td style="text-align:left">=IF(<font color="Blue">N(<font color="Red">L3</font>),INDEX(<font color="Red">$D:$J,MATCH(<font color="Green">L3,$C:$C,-1</font>),MATCH(<font color="Green">TEXT(<font color="Purple">L3,"ddd"</font>),$D$2:$J$2,0</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">L$3:L3</font>)+1<=$M$1,L3-1,""</font>)</td></tr></tbody></table></td></tr></table><br />
Copy L4 & M3 formulas.

If the dates in C:C are in ascending order change,

MATCH(L3,$C:$C,-1)

to,

MATCH(L3,$C:$C,1)

-1 to 1

 

[martindwilson]

hmm how it works
index(row number,column number)
for your list you need row 1 column 7 then row 1 col 6 ..to row 1 column 1
then you need row 2 column 7 then row 2 col 6 ..to row2 column 1 and so on
row(a1) or row(c1) will give that row number ie 1

CEILING(ROW(C1)/7,1), will give a list when dragged down of

CEILING(ROW(C1)/7,1)
CEILING(ROW(C2)/7,1)
CEILING(ROW(C3)/7,1)

what do you mean by oldest to newest? per week or the whole range
i;d just copy ..
CEILING(ROW(C7)/7,1)
which gives 1
1
1
1
1
1
1
1
CEILING(ROW(C8)/7,1)
then gives
2
2
2
2 ie 7 lots of 2 the it will change to 7 lots of 3 and so on
using that in the row part of the index will return from row 1, 7 times then row 2 times
what do you mean oldest to newest per week or for the whole range? id just copy col l paste back special values then sort by date column

 

[Access Beginner]

Thanks for that, will try decipher it!

I would prefer not to have to manipulate as I need it to autopopulate charts (and each week the data grows), I need the full date range reversed so desired result:

Sat, April 16, 2011 653
Sun, April 17, 2011 816
Mon, April 18, 2011 173
Tue, April 19, 2011 507
Wed, April 20, 2011 367
Thu, April 21, 2011 942
Fri, April 22, 2011 154
Sat, April 23, 2011 738
Sun, April 24, 2011 289
Mon, April 25, 2011 25
Tue, April 26, 2011 343
Wed, April 27, 2011 536
Thu, April 28, 2011 582
Fri, April 29, 2011 938

 

[Haseeb Avarakkan]

In the post #5, change these formulas,

M1,

=COUNT(C:C)*7

L3,

=MIN(C:C)-6

L4, Copy down.

=IF(ROWS(L$3:L3)+1<=$M$1,L3+1,"")

M3, copy down.

=IF(N(L3),INDEX($D:$J,MATCH(L3,$C:$C,-1),MATCH(TEXT(L3,"ddd"),$D$2:$J$2,0)),"")

 

[Access Beginner]

In the post #5, change these formulas,

M1,

=COUNT(C:C)*7

L3,

=MIN(C:C)-6

L4, Copy down.

=IF(ROWS(L$3:L3)+1<=$M$1,L3+1,"")

M3, copy down.

=IF(N(L3),INDEX($D:$J,MATCH(L3,$C:$C,-1),MATCH(TEXT(L3,"ddd"),$D$2:$J$2,0)),"")

Thanks Haseeb, I found just changing the date order worked fine in L column (I just manually entered the dates and formula works)

I would still like to hear martindwilson's response how to reverse his, as the data I paste in is in a txt/General format and would prefer not to have to convert it to dates to work with your formula (tho I admit your formula is easier to work with overall)

Cheers

 

[Haseeb Avarakkan]

Access Beginner,

In a blank cell just enter, =ISNUMBER(C3+0)

What is the result, TRUE or FALSE?

Also, what version of excel are you using?