Previous Topic Next Topic

Find last time text entered and return value

Solved/Closed
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012 - 5 Aug 2010 à 02:29
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012 - 10 Aug 2010 à 08:01
Hello,

I've got text in column D and a value for it in column G. I want to lookup/find the last time "Bolt 'was entered and return it's value.(the list in column D can have up to 300 rows of text in with values in G.

Column A Column D Column G
06/05/2010 07:33:53 Nuts 10
06/05/2010 11:53:43 Bolts 12
07/05/2010 14:20:30 Screws 15
08/05/2010 08:10:23 Bolts 13

So the bolts last entered value was 13 that must be returned.



Related:

2 responses

Réponse 1 / 2
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
5 Aug 2010 à 07:22
Try this Gouws

=SUMPRODUCT((G1:G10) * (D1:D10="Bolts") * (A1:A10=SUMPRODUCT(MAX((D1:D10="Bolts") * (A1:A10) )* 1)) *1 )
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012
6 Aug 2010 à 02:03
rivisa i've used =LOOKUP(2,1/($D$2:$D$9=H2),$G$2;$G$9) and put the name Bolts in H2. The only problem is when a negative number is entered.
I'm looking it up from the bottom to the top!
Réponse 2 / 2
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012
6 Aug 2010 à 00:17
Hi rivisa1

It give me : #VALUE back
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
6 Aug 2010 à 10:55
I used your sample on row 1:3

and used this formula for Bolts
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Bolts") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Bolts") * (A$1:A$10) )* 1)) *1 )

I got 13


use this formula for Screws
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Screws") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Screws") * (A$1:A$10) )* 1)) *1 )
and got 15


use this formula for nuts
=SUMPRODUCT((G$1:G$10) * (D$1:D$10="Nuts") * (A$1:A$10=SUMPRODUCT(MAX((D$1:D$10="Nuts") * (A$1:A$10) )* 1)) *1 )
and got 10


May be if you post a link to you file that would help
Gouws Posts 45 Registration date Sunday 7 February 2010 Status Member Last seen 15 April 2012
10 Aug 2010 à 08:01
TX, rivisa 1 found the problem it's working 100%

Similar discussions

WOW equipment return
aakash12345_0667 -
HelpiOS -

1 response
Connection keeps timing out
Kimber -
Jeff -

29 responses
The system time is invalid
LeonardPromise -
Anonymous User -

5 responses
We received your account.
baldevbhai -
dwebb -

1 response
Facebook says that my email doesn't match my facebook email
@@@@@@@@@@@ -
Ambucias -

1 response