| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i find the last specific value in a column?
i have 2 columns of data. the first is a box number from 1-15 the second is
the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
To find the last 9 in A1:A100:
Ctrl/shift/enter: =MAX(ROW(1:100)*(A1:A100=9)) Then use this value (say it's in E1) as input to the Index: =INDEX(B:B,E1) HTH Bob Umlas Excel MVP "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
Assuming the dates/times in column B are in ascending order (like they are
in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
This turned out to be the perfect solution for this query can you please
elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
Let's use this example to demonstrate how this works:
...........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ...........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
Worked perfectly.
Thanks for the help, I owe you a beer. BS "T. Valko" wrote: Let's use this example to demonstrate how this works: ...........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ...........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
You're welcome. Thanks for the feedback!
Biff "bradsmith37" wrote in message ... Worked perfectly. Thanks for the help, I owe you a beer. BS "T. Valko" wrote: Let's use this example to demonstrate how this works: ...........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ...........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
It is of great help..
Thanks a lot to guide me through such good example. Shweta Srivastava "T. Valko" wrote: Let's use this example to demonstrate how this works: ...........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ...........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
You're welcome. Thanks for the feedback!
Biff "Shweta Srivastava77" wrote in message ... It is of great help.. Thanks a lot to guide me through such good example. Shweta Srivastava "T. Valko" wrote: Let's use this example to demonstrate how this works: ...........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ...........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i find the last specific value in a column?
Op vrijdag 4 mei 2007 14:46:41 UTC-4 schreef T. Valko:
Let's use this example to demonstrate how this works: ..........A.............B 1.....header.....header 2........9.............10 3........7.............12 4........9.............15 5........5.............16 Return the value in column B that corresponds to the *last instance* of 9 in column A. =LOOKUP(2,1/(A2:A5=9),B2:B5) This expression will return an array of either TRUE or FALSE: (A2:A5=9) A2 = 9 = 9 = TRUE A3 = 7 = 9 = FALSE A4 = 9 = 9 = TRUE A5 = 5 = 9 = FALSE We then use the divison operation to coerce those logical values to numbers: A2 = 1 / TRUE = 1 A3 = 1 / FALSE = #DIV/0! A4 = 1 / TRUE = 1 A5 = 1 / FALSE = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression: 1/(A2:A5=9) will not return a value greater than 1. This is how that would look: ..........A.............B 1.....header.....header 2........1.............10 3...#DIV/0!.......12 4........1.............15 5...#DIV/0!.......16 So, the *last instance* of 9 was in A4. Return the corresponding value from B4. =LOOKUP(2,1/(A2:A5=9),B2:B5) = 15 Biff "Shweta Srivastava77" wrote in message ... This turned out to be the perfect solution for this query can you please elobarate the formula. As per the syntax it shoud be: LOOKUP(lookup_value,lookup_vector,result_vector) result vector that is B2:B13 (Date & Time) is understandable lookup_value = 2 ??Why so Lookup_vector = 1/(A2:A13=9)?? confused. Please explain?? Shweta Srivastava "T. Valko" wrote: Assuming the dates/times in column B are in ascending order (like they are in your example): =LOOKUP(2,1/(A2:A13=9),B2:B13) Format as DATE TIME Biff "bradsmith37" wrote in message ... i have 2 columns of data. the first is a box number from 1-15 the second is the time and date the part was weighed. i want to find the last time a box number was used, say 9, and have the corresponding date copied to another cell. thank you in advance for your help, BS. 8 4/25/2006 15:15 11 4/25/2006 15:16 9 4/25/2006 15:16 10 4/25/2006 15:17 15 4/25/2006 15:18 2 4/26/2006 7:15 9 4/26/2006 7:15 7 4/26/2006 7:16 5 4/26/2006 7:24 9 4/26/2006 7:24 14 4/26/2006 7:24 15 4/26/2006 7:26 Is there a way to make this formula work if you are dealing with text in column A versus numbers?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I find a specific value in a random column of numbers | Excel Worksheet Functions | |||
| find a specific value in a column and write another column | Excel Discussion (Misc queries) | |||
| How can I find and sort specific data within a column? | Excel Discussion (Misc queries) | |||
| Find column letter containing specific data | Excel Worksheet Functions | |||
| find formula that will look up a value in a specific column and . | New Users to Excel | |||
Linear Mode
