Converting Daily Info to Weekly
 

I am trying to "pull" certain information from a Daily Table into a Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open" figure
from the Daily Table into a Weekly Table, based on the criteria that as the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

Converting Daily Info to Weekly
 

My other suggestion worked but adding a helper column with the week num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
I am trying to "pull" certain information from a Daily Table into a Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open"
figure
from the Daily Table into a Weekly Table, based on the criteria that as
the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

Converting Daily Info to Weekly
 

Biff,

I have to say that I am very impressed. I am still not sure how the formula
works, but work it does!! I have one further "wrinkle" that I would like to
add, if I may: The data I am looking at is over a number of years and,
therefore, there are instances wher the WeekNum will be the same for
different years. How do I get the formula to pull up the data relevant to
the particular year only?

Many, many thanks, Gavin.

"T. Valko" wrote:

My other suggestion worked but adding a helper column with the week num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
I am trying to "pull" certain information from a Daily Table into a Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open"
figure
from the Daily Table into a Weekly Table, based on the criteria that as
the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

Converting Daily Info to Weekly
 

For the year 2008:

=LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9)

--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
Biff,

I have to say that I am very impressed. I am still not sure how the
formula
works, but work it does!! I have one further "wrinkle" that I would like
to
add, if I may: The data I am looking at is over a number of years and,
therefore, there are instances wher the WeekNum will be the same for
different years. How do I get the formula to pull up the data relevant to
the particular year only?

Many, many thanks, Gavin.

"T. Valko" wrote:

My other suggestion worked but adding a helper column with the week num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in
message
...
I am trying to "pull" certain information from a Daily Table into a
Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open"
figure
from the Daily Table into a Weekly Table, based on the criteria that as
the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

Converting Daily Info to Weekly
 

Biff,

Apologies for the delay in thanking you.

If I were to try and get the "Close" rather than the "Open" in a similar set
of data (i.e. it would be the final day in the particular WeekNum, as opposed
to the first), how might I do that?

Kind regards, Gavin.

"T. Valko" wrote:

For the year 2008:

=LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9)

--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
Biff,

I have to say that I am very impressed. I am still not sure how the
formula
works, but work it does!! I have one further "wrinkle" that I would like
to
add, if I may: The data I am looking at is over a number of years and,
therefore, there are instances wher the WeekNum will be the same for
different years. How do I get the formula to pull up the data relevant to
the particular year only?

Many, many thanks, Gavin.

"T. Valko" wrote:

My other suggestion worked but adding a helper column with the week num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in
message
...
I am trying to "pull" certain information from a Daily Table into a
Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open"
figure
from the Daily Table into a Weekly Table, based on the criteria that as
the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table will
automatically pull the Open figure (i.e. 515) into the relevant cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.

Converting Daily Info to Weekly
 

Based on the same table...

Try this array formula** entered in E2:

=INDEX(B$2:B$20,MATCH(1,($A$2:$A$20=$D2)*(YEAR($B$ 2:$B$20)=2008),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Copy across to F2 then down as needed.

Format E2:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
Biff,

Apologies for the delay in thanking you.

If I were to try and get the "Close" rather than the "Open" in a similar
set
of data (i.e. it would be the final day in the particular WeekNum, as
opposed
to the first), how might I do that?

Kind regards, Gavin.

"T. Valko" wrote:

For the year 2008:

=LOOKUP(2,1/(($A$1:$A$19=$D1)*(YEAR($B$1:$B$19)=2008)),B$1:B$1 9)

--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in
message
...
Biff,

I have to say that I am very impressed. I am still not sure how the
formula
works, but work it does!! I have one further "wrinkle" that I would
like
to
add, if I may: The data I am looking at is over a number of years and,
therefore, there are instances wher the WeekNum will be the same for
different years. How do I get the formula to pull up the data relevant
to
the particular year only?

Many, many thanks, Gavin.

"T. Valko" wrote:

My other suggestion worked but adding a helper column with the week
num
makes it easy!

If D1 = weeknum = 35

Enter this formula in E1 and copy across to F1:

=LOOKUP(2,1/($A$1:$A$19=$D1),B$1:B$19)

Select both E1 and F1 and copy down as needed.

Format E1:En as Date


--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in
message
...
I am trying to "pull" certain information from a Daily Table into a
Weekly
Table.

For example, in the two tables below, I am trying to pull the "Open"
figure
from the Daily Table into a Weekly Table, based on the criteria that
as
the
week changes (e.g. from WeekNum 34 to WeekNum 35) the Weekly Table
will
automatically pull the Open figure (i.e. 515) into the relevant
cell.

Daily Info Weekly Info

WeekNum Date Open WeekNum Date Open
35 29/08/08 558 35 26/08/08 515
35 28/08/08 526.5 34 18/08/08 610
35 27/08/08 524.5 33 11/08/08 603
35 26/08/08 515 32 04/08/08 538.5
34 22/08/08 522
34 21/08/08 540
34 20/08/08 560
34 19/08/08 576
34 18/08/08 610
33 15/08/08 608
33 14/08/08 581
33 13/08/08 602
33 12/08/08 630
33 11/08/08 603
32 08/08/08 598.5
32 07/08/08 607
32 06/08/08 624.5
32 05/08/08 540.5
32 04/08/08 538.5

This really as me stumped!! Any help would be appreciated.

Kind regards, Gavin.