Get last 6 rows in list where 1 cell matches certain criteria
 

Hi,
I have a large list of data and need to extract from that list the last 6
rows where one cell has a certain entry.
Example:

A B C D E
1 Date Name Data1 Data2 Data3
2 5/4/06 JONES RED BLUE YELLOW
3 18/4/06 BLAKE BLUE GREEN RED
4 26/4/06 JONES RED WHITE BLACK
5 2/5/06 SMITH YELLOW GREEN BLUE

Loads more data entries

595 3/3/08 JONES RED BLUE BLUE
596 5/3/08 BATES GREEN BLACK WHITE
597 7/3/08 SMITH BLUE RED RED
598 10/3/08 JONES BROWN BLACK ORANGE
599 12/3/08 BLAKE RED RED GREEN
600 14/3/08 BATES BLUE YELLOW BLUE

I need a formula that I can place somewhere below the list to auto-extract
just the last 6 rows in the listing where the name is JONES (to allow me to
do further work on Data1, 2, etc. without interfering with the original data).

Any help would be much appreciated.
Cheers,
Steve.

Get last 6 rows in list where 1 cell matches certain criteria
 

Use this array formula (Use Ctrl+Shift+Enter to confirm)

Place this into column A, whatever row you want underneath your data. Copy
down as many times as you want (in your case, 6). Copy over as far as
necessary to extract data. Note that this displays data in reverse order
(last, next to last, 2 before last, etc.)

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),ROW(A1)),COLUMN()))

If you don't want data reverse, you'll have to do a little more work. In the
first row, change the formula to

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),6),COLUMN()))

Next is

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),5),COLUMN()))

And so on, until you get down to 1.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Struggling in Sheffield" wrote:

Hi,
I have a large list of data and need to extract from that list the last 6
rows where one cell has a certain entry.
Example:

A B C D E
1 Date Name Data1 Data2 Data3
2 5/4/06 JONES RED BLUE YELLOW
3 18/4/06 BLAKE BLUE GREEN RED
4 26/4/06 JONES RED WHITE BLACK
5 2/5/06 SMITH YELLOW GREEN BLUE

Loads more data entries

595 3/3/08 JONES RED BLUE BLUE
596 5/3/08 BATES GREEN BLACK WHITE
597 7/3/08 SMITH BLUE RED RED
598 10/3/08 JONES BROWN BLACK ORANGE
599 12/3/08 BLAKE RED RED GREEN
600 14/3/08 BATES BLUE YELLOW BLUE

I need a formula that I can place somewhere below the list to auto-extract
just the last 6 rows in the listing where the name is JONES (to allow me to
do further work on Data1, 2, etc. without interfering with the original data).

Any help would be much appreciated.
Cheers,
Steve.

Get last 6 rows in list where 1 cell matches certain criteria
 

Hi Luke,
Had to prat around for a bit (due to other stuff going off in the
spreadsheet giving me problems) but finally got it to work as advertised, so
just wanted to say BIG thanks for your help.
All the best pal!
Steve.

"Luke M" wrote:

Use this array formula (Use Ctrl+Shift+Enter to confirm)

Place this into column A, whatever row you want underneath your data. Copy
down as many times as you want (in your case, 6). Copy over as far as
necessary to extract data. Note that this displays data in reverse order
(last, next to last, 2 before last, etc.)

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),ROW(A1)),COLUMN()))

If you don't want data reverse, you'll have to do a little more work. In the
first row, change the formula to

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),6),COLUMN()))

Next is

=INDIRECT(ADDRESS(LARGE(IF($B$2:$B$600="JONES",ROW ($B$2:$B$600)),5),COLUMN()))

And so on, until you get down to 1.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Struggling in Sheffield" wrote:

Hi,
I have a large list of data and need to extract from that list the last 6
rows where one cell has a certain entry.
Example:

A B C D E
1 Date Name Data1 Data2 Data3
2 5/4/06 JONES RED BLUE YELLOW
3 18/4/06 BLAKE BLUE GREEN RED
4 26/4/06 JONES RED WHITE BLACK
5 2/5/06 SMITH YELLOW GREEN BLUE

Loads more data entries

595 3/3/08 JONES RED BLUE BLUE
596 5/3/08 BATES GREEN BLACK WHITE
597 7/3/08 SMITH BLUE RED RED
598 10/3/08 JONES BROWN BLACK ORANGE
599 12/3/08 BLAKE RED RED GREEN
600 14/3/08 BATES BLUE YELLOW BLUE

I need a formula that I can place somewhere below the list to auto-extract
just the last 6 rows in the listing where the name is JONES (to allow me to
do further work on Data1, 2, etc. without interfering with the original data).

Any help would be much appreciated.
Cheers,
Steve.