Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

Hi Domenic,

Terrific!!

If possible, just one other tweak: can the order of the 3 rows of source data
be reversed. That is; the very first row be returned across the row first,
the second returned second and the very last of the 3 rows be returned last
across each single row.

Cheers,
Sam

Domenic wrote:
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!

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

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

Try replacing...

INT((COLUMN()-COLUMN($C2))/COLUMNS(Data))+1

with

3-(INT((COLUMN()-COLUMN($C2))/COLUMNS(Data)))

Hope this helps!

In article <6829659cce74c@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Terrific!!

If possible, just one other tweak: can the order of the 3 rows of source data
be reversed. That is; the very first row be returned across the row first,
the second returned second and the very last of the 3 rows be returned last
across each single row.

Cheers,
Sam

Return across Row Numeric Values Matching EXACT Month & Year for Criteria
 

Hi Domenic,

That's Perfect! Thank you so much.

Cheers,
Sam

Domenic wrote:
Try replacing...

INT((COLUMN()-COLUMN($C2))/COLUMNS(Data))+1

with

3-(INT((COLUMN()-COLUMN($C2))/COLUMNS(Data)))

Hope this helps!

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