Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
Hello,
I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
Try Auto Filter
" wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
On 4 jun, 16:06, Teethless mama
wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
How many rows of data are there? If there are 1000's then Autofilter is your
best option. Biff wrote in message ps.com... On 4 jun, 16:06, Teethless mama wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
On 4 jun, 17:27, "T. Valko" wrote:
How many rows of data are there? If there are 1000's then Autofilter is your best option. Biff wrote in message ps.com... On 4 jun, 16:06, Teethless mama wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq Biff, The amount of rows grow daily. Eventually, there will be 1000 rows, but the amount of rows that meet the criteria (for example Stock A) should not exceed 10. The problem I see about using auto filter is that there are many different stocks and they change in time. avializq |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
wrote in message ps.com... On 4 jun, 17:27, "T. Valko" wrote: How many rows of data are there? If there are 1000's then Autofilter is your best option. Biff wrote in message ps.com... On 4 jun, 16:06, Teethless mama wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq Biff, The amount of rows grow daily. Eventually, there will be 1000 rows, but the amount of rows that meet the criteria (for example Stock A) should not exceed 10. The problem I see about using auto filter is that there are many different stocks and they change in time. avializq Here's a sample file that demonstates this: Delete_Me.xls 18kb http://cjoint.com/?gffLHM0ch3 Enter a stock symbol in cell G2 and the bordered table will fill in. I've used a few defined names. To see these goto the menu InsertNameDefine. I entered the array formula** in cell H2 then copied across to J2 then down to row 11. As you add new data to the stock table the ranges will automatically adjust. This is based on the assumption that your stock table will be a contiguous block with no empty cells within. You'll have to see if this impacts the performance of your file. You said that there should only be about 10 entries that meet the criteria so I've copied the formula to only 10 rows. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) P.S. - that file name is for my own use! I have 100's of sample files and trying to come up with new unique names is a challenge. When I post sample files they're unique and specific to the subject of the post. After a few days I delete them so the file name lets me know which files I can delete. Biff |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
On 4 jun, 23:58, "T. Valko" wrote:
wrote in message ps.com... On 4 jun, 17:27, "T. Valko" wrote: How many rows of data are there? If there are 1000's then Autofilter is your best option. Biff wrote in message oups.com... On 4 jun, 16:06, Teethless mama wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq Biff, The amount of rows grow daily. Eventually, there will be 1000 rows, but the amount of rows that meet the criteria (for example Stock A) should not exceed 10. The problem I see about using auto filter is that there are many different stocks and they change in time. avializq Here's a sample file that demonstates this: Delete_Me.xls 18kb http://cjoint.com/?gffLHM0ch3 Enter a stock symbol in cell G2 and the bordered table will fill in. I've used a few defined names. To see these goto the menu InsertNameDefine. I entered the array formula** in cell H2 then copied across to J2 then down to row 11. As you add new data to the stock table the ranges will automatically adjust. This is based on the assumption that your stock table will be a contiguous block with no empty cells within. You'll have to see if this impacts the performance of your file. You said that there should only be about 10 entries that meet the criteria so I've copied the formula to only 10 rows. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) P.S. - that file name is for my own use! I have 100's of sample files and trying to come up with new unique names is a challenge. When I post sample files they're unique and specific to the subject of the post. After a few days I delete them so the file name lets me know which files I can delete. Biff Biff, Thank you very much. That is what I was looking for. I took the time to write out and analyse the formulas you used, and it wasn't easy. I think I understand how most of the formulas work, except for your definitions of Row, Stock and Table. Could you explain these? Thank you again for your time and effort, avializq |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup value and date from a list with restrictions
wrote in message ups.com... On 4 jun, 23:58, "T. Valko" wrote: wrote in message ps.com... On 4 jun, 17:27, "T. Valko" wrote: How many rows of data are there? If there are 1000's then Autofilter is your best option. Biff wrote in message oups.com... On 4 jun, 16:06, Teethless mama wrote: Try Auto Filter " wrote: Hello, I have the following problem. From a Data list that has a large amount of information registrated, such as: Stock Date Value Operation A 20-jan-07 100 buy B 25-jan-07 90 sell A 5-feb-07 150 buy C 10-feb-07 200 sell A 5-Mar-07 200 buy I need to recover from the Data list all information available about Stock A. What do you recommend? If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the date 20-jan-07 would appear If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha value 100 would appear. The problem is, how do I write a formula that finds the rest of the information availlable of Stock A. Previously I posted a similar question with two results. One suggested using Autofilter. For a manual solution thats ok, but this must be automatic. The Data list has a large amount of registers that grow on a daily basis. The second suggested using = LOOKUP (2, 1/(stock="A"), Value). I don't understand how this works, allow it does find the last value for A, but not the second. Please consider that more than three registers for A might exist. This is only an example. Thank you for the help, avializq TM, I understand that Auto Filter helps, but I'm looking for a formula. My Data list has too many records, and I need to look up many different field matches. avializq Biff, The amount of rows grow daily. Eventually, there will be 1000 rows, but the amount of rows that meet the criteria (for example Stock A) should not exceed 10. The problem I see about using auto filter is that there are many different stocks and they change in time. avializq Here's a sample file that demonstates this: Delete_Me.xls 18kb http://cjoint.com/?gffLHM0ch3 Enter a stock symbol in cell G2 and the bordered table will fill in. I've used a few defined names. To see these goto the menu InsertNameDefine. I entered the array formula** in cell H2 then copied across to J2 then down to row 11. As you add new data to the stock table the ranges will automatically adjust. This is based on the assumption that your stock table will be a contiguous block with no empty cells within. You'll have to see if this impacts the performance of your file. You said that there should only be about 10 entries that meet the criteria so I've copied the formula to only 10 rows. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) P.S. - that file name is for my own use! I have 100's of sample files and trying to come up with new unique names is a challenge. When I post sample files they're unique and specific to the subject of the post. After a few days I delete them so the file name lets me know which files I can delete. Biff Biff, Thank you very much. That is what I was looking for. I took the time to write out and analyse the formulas you used, and it wasn't easy. I think I understand how most of the formulas work, except for your definitions of Row, Stock and Table. Could you explain these? Thank you again for your time and effort, avializq All of those named formulas are used to define a dynamic range. Table refers to the actual data that you want to extract. Stock refers to the range of stock symbols. Stock is the "key" column It's assumed that for every entry is Stock there will be data in the other columns. So we use the "key" column to get the total number of rows of data in the entire table instead of counting the rows in every column. Row generates an array of numbers from 1 to n. n = the number of rows in Stock. This is used in the INDEX function to tell it which values to extract. Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet Restrictions | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Text Restrictions | Excel Worksheet Functions | |||
data restrictions | Excel Discussion (Misc queries) | |||
How to do look up with restrictions | Excel Discussion (Misc queries) |