Return Last 10 rows in reverse row order.

[Sam via OfficeKB.com]

Hi All,

I have an 8 column by many rows, dynamic named range called "Data". The named
range starts in row 2, column "C". The oldest data is at the top, row 2 and
the most recent data is at the bottom of the named range. "Data" holds
numeric values.

Scenario:
I would like a formula using the named range, "Data", to return the most
recent, last 10 rows from the bottom of the named range in reverse row order.

Sample Data Layout: Rows 6-15 inclusive cover the last 10 rows.

Row1 111 114 116 125
Row2 118 112 117 123
Row3 116 110 114 125
Row4 118 110 116 117
Row5 116 120 129 131
Row6 112 119 128 131
Row7 112 119 122 123
Row8 112 118 116 121
Row9 112 119 113 118
Row10 115 118 121 138
Row11 113 121 123 125
Row12 113 119 124 125
Row13 112 115 119 128
Row14 118 119 121 130
Row15 112 114 121 125

Expected Results:
Return last 10 rows, rows 6-15 in reverse row order. Last row, row15 should
be returned 1st.

Row15 112 114 121 125
Row14 118 119 121 130
Row13 112 115 119 128
Row12 113 119 124 125
Row11 113 121 123 125
Row10 115 118 121 138
Row9 112 119 113 118
Row8 112 118 116 121
Row7 112 119 122 123
Row6 112 119 128 131

Thanks
Sam

 

[Roger Govier]

Hi Sam
Try
=INDEX(Data,COUNTA($C:$C)-(ROW(A1)-1),COLUMN(A1))

I used COUNTA on your sample, if the data is numeric you could also use
COUNT
COUNT would ignore any text in row 1
Copy the formula across through 7 columns to get your 8 values, then copy
all 8 formulae down through a further 5 rows

 

[Sam via OfficeKB.com]

Hi Roger,

Thank you very much for reply and assistance.

I tried your formula but it didn't give me the required results. The first
row it returned was the third from the top of my named range "Data". I think
because of the COUNTA($C:$C). Also, the rows of data should be returned from
the bottom of the named range.

However, I played about with your formula and came up with this (fill across
and down):

=INDEX(Data,ROWS(Data)-ROW(1:1)+1,COLUMN(A1))

It returns the rows of data in reverse order as required.

Thanks again for giving me something to work with.

Cheers,
Sam

 

[Roger Govier]

Sam

The formula I gave would return the rows starting from the end and working
backward
I pasted the formula in cell N2 of my Sheet and returned Row15 of your data
going downward.
I did say to switch to COUNT, if your data was all numeric apart from a
header, as opposed to Text data in Column C as posted in your example.

No matter, the solution you came up with is essentially the same, and I am
glad it resolved your problem.

 

[Sam via OfficeKB.com]

Hi Roger,

Sam

The formula I gave would return the rows starting from the end and working
backward
I pasted the formula in cell N2 of my Sheet and returned Row15 of your data
going downward.

Tried it again, still no joy. Not sure what went wrong.

I did say to switch to COUNT, if your data was all numeric apart from a
header, as opposed to Text data in Column C as posted in your example.

I tried both COUNTA and COUNT. Apart from a header in row 1, there isn't any
text in column "C" of my sample data layout. Not sure where you see the text.
The row number reference down the side of the sample data is only there as a
visual aide.

No matter, the solution you came up with is essentially the same, and I am
glad it resolved your problem.

Thanks again.

Cheers,
Sam

 

[Roger Govier]

Sam

This has me baffled
I copied the data in exactly as posted, using the "visual aide" column as
well, which was in Column C
If your column C contains 1 header in row 1 and only your numeric data in
the remainder of column C, and if there is no data in column C below your
Dynamic range Data, then COUNT($C:$C) should return the same value as
ROWS(Data)
In all other respects the formulae are identical.

 

[Ron Rosenfeld]

Hi All,

I have an 8 column by many rows, dynamic named range called "Data". The named
range starts in row 2, column "C". The oldest data is at the top, row 2 and
the most recent data is at the bottom of the named range. "Data" holds
numeric values.

Scenario:
I would like a formula using the named range, "Data", to return the most
recent, last 10 rows from the bottom of the named range in reverse row order.

Sample Data Layout: Rows 6-15 inclusive cover the last 10 rows.

Row1 111 114 116 125
Row2 118 112 117 123
Row3 116 110 114 125
Row4 118 110 116 117
Row5 116 120 129 131
Row6 112 119 128 131
Row7 112 119 122 123
Row8 112 118 116 121
Row9 112 119 113 118
Row10 115 118 121 138
Row11 113 121 123 125
Row12 113 119 124 125
Row13 112 115 119 128
Row14 118 119 121 130
Row15 112 114 121 125

Expected Results:
Return last 10 rows, rows 6-15 in reverse row order. Last row, row15 should
be returned 1st.

Row15 112 114 121 125
Row14 118 119 121 130
Row13 112 115 119 128
Row12 113 119 124 125
Row11 113 121 123 125
Row10 115 118 121 138
Row9 112 119 113 118
Row8 112 118 116 121
Row7 112 119 122 123
Row6 112 119 128 131

Thanks
Sam

Enter this formula in some cell:

=INDEX(Data,ROWS(Data)-ROWS($1:1)+1,COLUMNS($A:A))

Fill down ten rows and across eight rows.


If you need to check that you don't fill down more than ten or across more than
the number of columns, you can use this instead, which returns a null string if
you go to far:

=IF(OR(COLUMNS($A:A)>COLUMNS(Data),
(ROWS(Data)-ROWS($1:1)+1)<=(ROWS(Data)-10)),"",
INDEX(Data,ROWS(Data)-ROWS($1:1)+1,COLUMNS($A:A)))
--ron

 

[Sam via OfficeKB.com]

Hi Roger,

You found my problem, I've got data below the dynamic range. I forgot about
it. Sorry!

Cheers,
Sam

Roger Govier wrote:
Sam
This has me baffled

I copied the data in exactly as posted, using the "visual aide" column as
well, which was in Column C
If your column C contains 1 header in row 1 and only your numeric data in
the remainder of column C, and if there is no data in column C below your
Dynamic range Data, then COUNT($C:$C) should return the same value as
ROWS(Data)

 

[Sam via OfficeKB.com]

Hi Ron,

Thanks a lot for additional input. Much appreciated.

Cheers,
Sam

Ron Rosenfeld wrote:

Enter this formula in some cell:

Fill down ten rows and across eight rows.

 

[Roger Govier]

Thanks for letting me know, Sam.
I thought that had to be the reason for it not working for you.