Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i need to get all matches in one column for one criteria | Excel Worksheet Functions | |||
Looking up a maximum value that matches criteria. VLOOKUP? | Excel Worksheet Functions | |||
Combobox list - only with rows that fit the criteria | Excel Discussion (Misc queries) | |||
SumIf Criteria Matches | Excel Worksheet Functions | |||
Is it possible to do a vertical lookup that matches on 2 criteria | Excel Worksheet Functions |