Return Last 10 rows in reverse row order.

  • Thread starter Sam via OfficeKB.com
  • Start date
S

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
 
R

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
 
S

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
 
R

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.
 
S

Sam via OfficeKB.com

Hi Roger,

Roger said:
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
 
R

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.
 
R

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
 
S

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)
 
R

Roger Govier

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top