Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,722
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 66
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
i need to get all matches in one column for one criteria PERANISH Excel Worksheet Functions 2 November 13th 08 01:40 AM
Looking up a maximum value that matches criteria. VLOOKUP? AndyCotgreave Excel Worksheet Functions 4 October 2nd 07 07:36 PM
Combobox list - only with rows that fit the criteria Vikram Excel Discussion (Misc queries) 0 July 28th 06 12:43 AM
SumIf Criteria Matches Debbie Dies Excel Worksheet Functions 4 August 1st 05 11:18 PM
Is it possible to do a vertical lookup that matches on 2 criteria lshaw Excel Worksheet Functions 4 May 16th 05 07:00 PM


All times are GMT +1. The time now is 12:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"