| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up last and next entry
I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10)
in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up last and next entry
Please note that these formulas are array formulas. You create array formulas
in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" In cell E1 enter the code 'To return the last values based on date from ColC and ColD copy this formula and copy across to the right =INDEX(OFFSET(C$1,0,0,MATCH(TODAY(),$A:$A,0),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)=$ E$1,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)) ),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1 ),$E$1)+ROW(A1)-1)) 'To return the next entry copy thebelow formula and copy to the right cell.. =INDEX(C:C,SMALL(IF($B$1:$B$65535=$E$1,ROW($B$1:$B $65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A: $A,0),1),$E$1)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10) in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. What I would like to do is based on todays date show the last time the code was used before today along with the data in column B & C, then show the next time the code is used from today onwards along with the data in column B & C. Thanks for the help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up last and next entry
Try these...
list of dates (from 1/1/08 - 31/12/10) in order Assumming the range of dates is A2:A1097 E2 = lookup code For the last instance *before* today: =LOOKUP(2,1/(B2:INDEX(B2:B1097,MATCH(NOW(),A2:A1097)-1)=E2),C2:INDEX(C2:C1097,MATCH(NOW(),A2:A1097)-1)) For the next instance: =VLOOKUP(E2,C1097:INDEX(B2:B1097,MATCH(TODAY(),A2: A1097)),2,0) -- Biff Microsoft Excel MVP "Huggy" wrote in message ... I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10) in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. What I would like to do is based on todays date show the last time the code was used before today along with the data in column B & C, then show the next time the code is used from today onwards along with the data in column B & C. Thanks for the help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up last and next entry
Hi Jacob,
Looks excellent. However, could you please check the code you have in G2. if I select the code that is at the top of the list if return with the header and if I select a code that is in the middle of the list it returns with the one above it. All the other codes work fine. Thanks Mike "Jacob Skaria" wrote: --I tried with your data in ColA to ColD..headers in Row1 --In the same sheet in from ColF to ColJ I have setup the headers in row1 and colF Col F Col G Col H Col I Col J Code Date L Del date Date N Del date PR 29-Oct-09 11-Nov 5-Nov 12-Oct DR 25-Oct-09 11-Oct 30-Nov 12-Sep SN 21-Oct-09 30/10 2-Nov 11-Oct --Try these formulas in row 2 and copy down as required..All are array formulas. Use Ctrl+Shift+Enter..If no match is found #NUM error is returned..You will need to handle this.. In G2 =INDEX(OFFSET(A$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In H2 =INDEX(OFFSET(D$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In I2 =INDEX(A:A,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) In J2 =INDEX(D:D,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Thanks for the help. Not quite what I was after. I'll try and make my question clearer. Exapmple of the data I have is; (I keep adding to this list of dates each year) Date Code Description 1 Delivery Date 20/10 SN Swan 28/10 21/10 DR Dream 30/10 25/10 PR Pioneer 10/11 29/10 ST Swift 11/11 2/11 SN Swan 10/11 3/11 SN Swan 28/11 5/11 PR Pioneer 10/12 29/11 ST Swift 11/12 30/11 DR Dream 9/12 I would like based on above data and starting from todays date complete a table which shows the last time and the next time the codes are used. Example table; which has the codes listed already for lookup reference. The date and delivery date data is what I need to lookup from the data above. Only the last time the code was used and the next time it will be used, then bring the result and show on the table below ----last time used--- ----next time used----- Code Date Delivery date Date Delivery date PR 25/10 10/11 5/11 10/12 DR 21/10 30/10 30/11 9/12 SN 20/10 28/10 3/11 28/11 ST 29/10 11/11 29/11 11/12 Hope this is a little clearer. Thanks again. "Jacob Skaria" wrote: Please note that these formulas are array formulas. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" In cell E1 enter the code 'To return the last values based on date from ColC and ColD copy this formula and copy across to the right =INDEX(OFFSET(C$1,0,0,MATCH(TODAY(),$A:$A,0),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)=$ E$1,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)) ),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1 ),$E$1)+ROW(A1)-1)) 'To return the next entry copy thebelow formula and copy to the right cell.. =INDEX(C:C,SMALL(IF($B$1:$B$65535=$E$1,ROW($B$1:$B $65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A: $A,0),1),$E$1)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10) in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. What I would like to do is based on todays date show the last time the code was used before today along with the data in column B & C, then show the next time the code is used from today onwards along with the data in column B & C. Thanks for the help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up last and next entry
Hi Jacob,
Thanks for your help. Looks excellent. However could you please check the code you have in cell G2. It returns the header of column A or a date earlier than the related code in column F. I copied and pasted the code you provided, so I don't have any typos. All the other codes work fine. Thanks again "Jacob Skaria" wrote: --I tried with your data in ColA to ColD..headers in Row1 --In the same sheet in from ColF to ColJ I have setup the headers in row1 and colF Col F Col G Col H Col I Col J Code Date L Del date Date N Del date PR 29-Oct-09 11-Nov 5-Nov 12-Oct DR 25-Oct-09 11-Oct 30-Nov 12-Sep SN 21-Oct-09 30/10 2-Nov 11-Oct --Try these formulas in row 2 and copy down as required..All are array formulas. Use Ctrl+Shift+Enter..If no match is found #NUM error is returned..You will need to handle this.. In G2 =INDEX(OFFSET(A$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In H2 =INDEX(OFFSET(D$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In I2 =INDEX(A:A,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) In J2 =INDEX(D:D,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Thanks for the help. Not quite what I was after. I'll try and make my question clearer. Exapmple of the data I have is; (I keep adding to this list of dates each year) Date Code Description 1 Delivery Date 20/10 SN Swan 28/10 21/10 DR Dream 30/10 25/10 PR Pioneer 10/11 29/10 ST Swift 11/11 2/11 SN Swan 10/11 3/11 SN Swan 28/11 5/11 PR Pioneer 10/12 29/11 ST Swift 11/12 30/11 DR Dream 9/12 I would like based on above data and starting from todays date complete a table which shows the last time and the next time the codes are used. Example table; which has the codes listed already for lookup reference. The date and delivery date data is what I need to lookup from the data above. Only the last time the code was used and the next time it will be used, then bring the result and show on the table below ----last time used--- ----next time used----- Code Date Delivery date Date Delivery date PR 25/10 10/11 5/11 10/12 DR 21/10 30/10 30/11 9/12 SN 20/10 28/10 3/11 28/11 ST 29/10 11/11 29/11 11/12 Hope this is a little clearer. Thanks again. "Jacob Skaria" wrote: Please note that these formulas are array formulas. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" In cell E1 enter the code 'To return the last values based on date from ColC and ColD copy this formula and copy across to the right =INDEX(OFFSET(C$1,0,0,MATCH(TODAY(),$A:$A,0),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)=$ E$1,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)) ),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1 ),$E$1)+ROW(A1)-1)) 'To return the next entry copy thebelow formula and copy to the right cell.. =INDEX(C:C,SMALL(IF($B$1:$B$65535=$E$1,ROW($B$1:$B $65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A: $A,0),1),$E$1)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10) in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. What I would like to do is based on todays date show the last time the code was used before today along with the data in column B & C, then show the next time the code is used from today onwards along with the data in column B & C. Thanks for the help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Look up last and next entry
Hi Jacob,
Please disregard my note below. It all works. With all the copying and changing I forgot to CTRL+SHIFT+ENTER to enter the formula. Great formula. Does exactly what I need. Thank you. "Huggy" wrote: Hi Jacob, Thanks for your help. Looks excellent. However could you please check the code you have in cell G2. It returns the header of column A or a date earlier than the related code in column F. I copied and pasted the code you provided, so I don't have any typos. All the other codes work fine. Thanks again "Jacob Skaria" wrote: --I tried with your data in ColA to ColD..headers in Row1 --In the same sheet in from ColF to ColJ I have setup the headers in row1 and colF Col F Col G Col H Col I Col J Code Date L Del date Date N Del date PR 29-Oct-09 11-Nov 5-Nov 12-Oct DR 25-Oct-09 11-Oct 30-Nov 12-Sep SN 21-Oct-09 30/10 2-Nov 11-Oct --Try these formulas in row 2 and copy down as required..All are array formulas. Use Ctrl+Shift+Enter..If no match is found #NUM error is returned..You will need to handle this.. In G2 =INDEX(OFFSET(A$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In H2 =INDEX(OFFSET(D$1,0,0,MATCH(TODAY(),$A:$A,1),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$ F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))) ,COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1) ,$F2))) In I2 =INDEX(A:A,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) In J2 =INDEX(D:D,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$ 65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$ A,1),1),$F2)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: Thanks for the help. Not quite what I was after. I'll try and make my question clearer. Exapmple of the data I have is; (I keep adding to this list of dates each year) Date Code Description 1 Delivery Date 20/10 SN Swan 28/10 21/10 DR Dream 30/10 25/10 PR Pioneer 10/11 29/10 ST Swift 11/11 2/11 SN Swan 10/11 3/11 SN Swan 28/11 5/11 PR Pioneer 10/12 29/11 ST Swift 11/12 30/11 DR Dream 9/12 I would like based on above data and starting from todays date complete a table which shows the last time and the next time the codes are used. Example table; which has the codes listed already for lookup reference. The date and delivery date data is what I need to lookup from the data above. Only the last time the code was used and the next time it will be used, then bring the result and show on the table below ----last time used--- ----next time used----- Code Date Delivery date Date Delivery date PR 25/10 10/11 5/11 10/12 DR 21/10 30/10 30/11 9/12 SN 20/10 28/10 3/11 28/11 ST 29/10 11/11 29/11 11/12 Hope this is a little clearer. Thanks again. "Jacob Skaria" wrote: Please note that these formulas are array formulas. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" In cell E1 enter the code 'To return the last values based on date from ColC and ColD copy this formula and copy across to the right =INDEX(OFFSET(C$1,0,0,MATCH(TODAY(),$A:$A,0),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)=$ E$1,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)) ),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1 ),$E$1)+ROW(A1)-1)) 'To return the next entry copy thebelow formula and copy to the right cell.. =INDEX(C:C,SMALL(IF($B$1:$B$65535=$E$1,ROW($B$1:$B $65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A: $A,0),1),$E$1)+1)) If this post helps click Yes --------------- Jacob Skaria "Huggy" wrote: I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10) in order and column B a list of codes. Each codes periodically is repeasted in no fixed order. Column C and D have data that relate to the code in column B. What I would like to do is based on todays date show the last time the code was used before today along with the data in column B & C, then show the next time the code is used from today onwards along with the data in column B & C. Thanks for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
| Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
| Auto entry of data based on entry of text in another column or fie | Excel Discussion (Misc queries) | |||
| How do I set up entry box to auto-alphabatize each entry in list? | Excel Discussion (Misc queries) | |||
| Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
Linear Mode
