Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 3rd 07, 08:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 2
Default 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   Report Post  
Old May 3rd 07, 09:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 301
Default 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   Report Post  
Old May 3rd 07, 11:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old May 4th 07, 11:34 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 12
Default 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   Report Post  
Old May 4th 07, 07:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old May 4th 07, 08:20 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 2
Default 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   Report Post  
Old May 4th 07, 10:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default how do i find the last specific value in a column?

You're welcome. Thanks for the feedback!

Biff

"bradsmith37" wrote in message
news
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   Report Post  
Old May 7th 07, 07:30 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 12
Default 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   Report Post  
Old May 7th 07, 06:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old June 23rd 20, 05:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2020
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I find a specific value in a random column of numbers yazmaz Excel Worksheet Functions 4 April 17th 07 03:55 PM
find a specific value in a column and write another column MUSTANG Excel Discussion (Misc queries) 2 February 5th 06 10:24 AM
How can I find and sort specific data within a column? bpreas - ExcelForums.com Excel Discussion (Misc queries) 3 August 2nd 05 07:11 PM
Find column letter containing specific data markx Excel Worksheet Functions 4 March 17th 05 11:41 PM
find formula that will look up a value in a specific column and . ksgirl89 New Users to Excel 1 January 30th 05 02:33 PM


All times are GMT +1. The time now is 01:57 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017