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

[Sam via OfficeKB.com]

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

 

[Domenic]

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.

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for reply.

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.

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

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

Cheers,
Sam

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

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

Try...

 

[Domenic]

Try replacing...

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

with

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

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

That's Perfect! Thank you so much.

Cheers,
Sam