Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows - houses
numeric values. I also have a single column dynamic range called "Date".
The values in
"Date" are formatted as 11/06/2006.

I would like "Data's" LAST 3 rows of numeric values for each month returned
using an EXACT month and year for the criteria. Each months LAST 3 rows of
numeric values matching criteria (month & year) should be returned across a
single row. For instance, return:
LAST 3 rows of numeric values in 01/2006 (January 2006) across a single row;
LAST 3 rows of numeric values in 02/2006 (February 2006) across a single row
;
LAST 3 rows of numeric values in 03/2006 (March 2006) across a single row etc.


The numeric values are to be returned to a worksheet that houses the month &
year criteria in column "B". The values in column "B" are formatted as
11/06/2006.

Thanks
Sam

--
Message posted via http://www.officekb.com

For each month, which of the following three methods would you prefer...

1) The data for all three rows returned in 30 consecutive cells, all in
ascending order.

2) The data for the last row returned in 10 consecutive cells, in
ascending order, then the data for the second last row returned in the
next 10 consecutive cells, in ascending order, and lastly the data for
the third last row returned in the next 10 consecutive cells, in
ascending order.

3) The data for the last row returned in 10 consecutive cells, in the
order in which they appear, then the data for the second last row
returned in the next 10 consecutive cells, in the order in which they
appear, and lastly the data for the third last row returned in the next
10 consecutive cells, in the order in which they appear.

Note that the first one is less efficient than the other two.

In article <681e663637a14@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows - houses
numeric values. I also have a single column dynamic range called "Date".
The values in
"Date" are formatted as 11/06/2006.

I would like "Data's" LAST 3 rows of numeric values for each month returned
using an EXACT month and year for the criteria. Each months LAST 3 rows of
numeric values matching criteria (month & year) should be returned across a
single row. For instance, return:
LAST 3 rows of numeric values in 01/2006 (January 2006) across a single row;
LAST 3 rows of numeric values in 02/2006 (February 2006) across a single row
;
LAST 3 rows of numeric values in 03/2006 (March 2006) across a single row etc.


The numeric values are to be returned to a worksheet that houses the month &
year criteria in column "B". The values in column "B" are formatted as
11/06/2006.

Thanks
Sam

Hi Domenic,

Thanks for reply.

Domenic wrote:
For each month, which of the following three methods would you prefer...

The data is already in ascending order, your number 3 fits well.

3) The data for the last row returned in 10 consecutive cells, in the
order in which they appear, then the data for the second last row
returned in the next 10 consecutive cells, in the order in which they
appear, and lastly the data for the third last row returned in the next
10 consecutive cells, in the order in which they appear.

Cheers,
Sam

For each month, which of the following three methods would you prefer...

1) The data for all three rows returned in 30 consecutive cells, all in
ascending order.

2) The data for the last row returned in 10 consecutive cells, in
ascending order, then the data for the second last row returned in the
next 10 consecutive cells, in ascending order, and lastly the data for
the third last row returned in the next 10 consecutive cells, in
ascending order.

3) The data for the last row returned in 10 consecutive cells, in the
order in which they appear, then the data for the second last row
returned in the next 10 consecutive cells, in the order in which they
appear, and lastly the data for the third last row returned in the next
10 consecutive cells, in the order in which they appear.

Note that the first one is less efficient than the other two.

--
Message posted via http://www.officekb.com

Assuming that the results start at Row 2, try the following...

C2, copied down and across to Column L:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),1),COLUMNS($C2:C2))

M2, copied down and across to Column V:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),2),COLUMNS($C2:C2))

W2, copied down and across to Column AF:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),3),COLUMNS($C2:C2))

Note that if Column B contains the first day of the month/year of
interest (June 2006, July 2006, etc), replace...

DATE(YEAR($B2),MONTH($B2),1)

with

$B2

....for each formula. Also, if a third row for any month/year doesn't
exist, the formula will return #NUM!. These errors can be hidden by
using conditional formatting.

Hope this helps!

In article <6825dadc60b04@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thanks for reply.

Domenic wrote:
For each month, which of the following three methods would you prefer...

The data is already in ascending order, your number 3 fits well.

3) The data for the last row returned in 10 consecutive cells, in the
order in which they appear, then the data for the second last row
returned in the next 10 consecutive cells, in the order in which they
appear, and lastly the data for the third last row returned in the next
10 consecutive cells, in the order in which they appear.

Cheers,
Sam

Hi Domenic,

This is Great! But is it possible to have a single Formula - pushing my luck!

Cheers,
Sam

Domenic wrote:
Assuming that the results start at Row 2, try the following...

C2, copied down and across to Column L:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),1),COLUMNS($C2:C2))

M2, copied down and across to Column V:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),2),COLUMNS($C2:C2))

W2, copied down and across to Column AF:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),3),COLUMNS($C2:C2))

Note that if Column B contains the first day of the month/year of
interest (June 2006, July 2006, etc), replace...

DATE(YEAR($B2),MONTH($B2),1)

with

$B2

...for each formula. Also, if a third row for any month/year doesn't
exist, the formula will return #NUM!. These errors can be hidden by
using conditional formatting.

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200610/1

Try...

C2, copied across and down:

=INDEX(Data,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($B2),MONTH($B2),1),ROW(Da
ta)-MIN(ROW(Data))+1),INT((COLUMN()-COLUMN($C2))/COLUMNS(Data))+1),MOD(CO
LUMN()-COLUMN($C2),COLUMNS(Data))+1)

....confirmed with CONTROL+SHIFT+ENTER. Note that if a new column of
data is added to the existing source data, the formula can be copied to
the next three columns, Column AG, Column AH, and Column AI. So, for
every new column of data added, the formula can be copied to the next
three columns.

Hope this helps!

In article <682780d414da4@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

This is Great! But is it possible to have a single Formula - pushing my luck!

Cheers,
Sam