|
how do i find the earliest entry in a table
Probably dead easy but its got me stumped.
Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
Hi,
Try this with the project number your looking for in C1, change the ranges to suit your data but they must all be the same size. =MIN(IF(B1:B20=C1,IF(A1:A20<"",A1:A20))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MCscrewdriver" wrote: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
=MATCH(MIN(A:A),A:A,0)
returns row number of the earliest date. Regards, Stefi €˛MCscrewdriver€¯ ezt Ć*rta: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
Thanks Mike,
Unfortunately this array formula only returns 0 so i think it must be down to my initial explanation - i'll try again. this is an excerpt of the table: PA date Project 13-Jan-2006 R102 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2006 E110 13-Jan-2006 E110 Projects may appear several times in any period due to a number of employees booking time in the same week. In subsequent weeks the same projects will be booked to and i need to determine the first time each project was booked to. Hope this is clearer. "Mike H" wrote: Hi, Try this with the project number your looking for in C1, change the ranges to suit your data but they must all be the same size. =MIN(IF(B1:B20=C1,IF(A1:A20<"",A1:A20))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MCscrewdriver" wrote: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
Hi,
So I change you sample data to the table below and have E110 in C1. My formula returns 13 - Jan - 2002 i.e the earliest dat for project E110 If your getting 0 then are you sure you array entered the formula correctly? Read these instructions again and enter the formula as an array This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. 13-Jan-2006 R102 E110 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2002 E110 13-Jan-2006 E110 Mike "MCscrewdriver" wrote: Thanks Mike, Unfortunately this array formula only returns 0 so i think it must be down to my initial explanation - i'll try again. this is an excerpt of the table: PA date Project 13-Jan-2006 R102 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2006 E110 13-Jan-2006 E110 Projects may appear several times in any period due to a number of employees booking time in the same week. In subsequent weeks the same projects will be booked to and i need to determine the first time each project was booked to. Hope this is clearer. "Mike H" wrote: Hi, Try this with the project number your looking for in C1, change the ranges to suit your data but they must all be the same size. =MIN(IF(B1:B20=C1,IF(A1:A20<"",A1:A20))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MCscrewdriver" wrote: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
Thanks Mike - problem sorted
"Mike H" wrote: Hi, So I change you sample data to the table below and have E110 in C1. My formula returns 13 - Jan - 2002 i.e the earliest dat for project E110 If your getting 0 then are you sure you array entered the formula correctly? Read these instructions again and enter the formula as an array This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. 13-Jan-2006 R102 E110 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2002 E110 13-Jan-2006 E110 Mike "MCscrewdriver" wrote: Thanks Mike, Unfortunately this array formula only returns 0 so i think it must be down to my initial explanation - i'll try again. this is an excerpt of the table: PA date Project 13-Jan-2006 R102 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2006 E110 13-Jan-2006 E110 Projects may appear several times in any period due to a number of employees booking time in the same week. In subsequent weeks the same projects will be booked to and i need to determine the first time each project was booked to. Hope this is clearer. "Mike H" wrote: Hi, Try this with the project number your looking for in C1, change the ranges to suit your data but they must all be the same size. =MIN(IF(B1:B20=C1,IF(A1:A20<"",A1:A20))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MCscrewdriver" wrote: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
how do i find the earliest entry in a table
Glad I could help
"MCscrewdriver" wrote: Thanks Mike - problem sorted "Mike H" wrote: Hi, So I change you sample data to the table below and have E110 in C1. My formula returns 13 - Jan - 2002 i.e the earliest dat for project E110 If your getting 0 then are you sure you array entered the formula correctly? Read these instructions again and enter the formula as an array This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. 13-Jan-2006 R102 E110 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2002 E110 13-Jan-2006 E110 Mike "MCscrewdriver" wrote: Thanks Mike, Unfortunately this array formula only returns 0 so i think it must be down to my initial explanation - i'll try again. this is an excerpt of the table: PA date Project 13-Jan-2006 R102 13-Jan-2006 R104 13-Jan-2006 R105 13-Jan-2006 R111 13-Jan-2006 R112 13-Jan-2006 R113 13-Jan-2006 R130 13-Jan-2006 E114 13-Jan-2006 E110 13-Jan-2006 E110 13-Jan-2006 E110 Projects may appear several times in any period due to a number of employees booking time in the same week. In subsequent weeks the same projects will be booked to and i need to determine the first time each project was booked to. Hope this is clearer. "Mike H" wrote: Hi, Try this with the project number your looking for in C1, change the ranges to suit your data but they must all be the same size. =MIN(IF(B1:B20=C1,IF(A1:A20<"",A1:A20))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MCscrewdriver" wrote: Probably dead easy but its got me stumped. Each week hours are booked to a number of different projects by 20 or more people. I need to identify the first date any time was booked to a particular project. Column A is the list of dates and column B is the project reference number. neither of the columns are in order. once i get this sorted i then need to use different tables of rates depending on the start date to calculate project costs (but more of that later) |
| All times are GMT +1. The time now is 04:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com