Finding the last value entered for an entity

[haas786]

Hi all!

I was wondering if I can get some Excel help for a problem I have
encountered:

Below is a list of the field names corresponding to the columns in
Excel (separated by comma's):

LastName, FirstName, RepID, Sales, UpdateNumber

I input information from various sources into this spreadsheet. I have
another spreadsheet set up which uses the data from above to do some
calculations. To make my request simple, I'll provide an example.

Let's say I have the following 10 entries in the database:

LastName, FirstName, RepID, Sales, UpdateNumber
Miller, John, CA23, 52000, 1
Talbot, Tom, NJ12, 7020, 1
Kowal, Jen, AZ13, 10900, 1
Miller, John, CA23, 64000, 2
Lamb, Jeff, NJ29, 493440, 1
Miller, John CA23, 89000, 3
Allen, Kevin, PA22, 90800, 1
Lamb, Jeff, NJ29, 3232444, 2
Kowal, Jen, AZ13, 15000, 2
Talbot, Tom, NJ12, 50000, 2

Any time there's an update for a particular salesperson, the
UpdateNumbers adds 1 to the last UpdateNumber. So, if you look above,
John Miller's UpdateNumber has reached 3 - for 3 updates. This would
be the entry I'm interested in. So, on my other spreadsheet which i
use to perform calculations, I would like to pull up the entry from
above list for John to reflect the latest sale (89,000). How would i
achieve that? Is there a way to figure out the max of the UpdateNumber
based on code and then doing a Vlookup against that? What I do is find
the latest sales figure from the above table? It is not necessarily a
larger figure or else I could do a MAX function alongwith a vlookup or
something. Any or all help with be greatly appreciated!

Thanks much!

 

[JMB]

Say your table is in A1:E11 (w/headers in row 1). I assume you would want to
look up the sales based on RepID (instead of name, which may not be unique)

C2:C11 = Rep ID
D211 = Sales
E2:E11 = Update Number
G1 = Rep ID number you want to find

Try:
=SUMPRODUCT(--(C2:C11=G1),--(E2:E11=MAX((C2:C11=G1)*E2:E11)),D211)


although if the same update number is entered twice for the same rep, you'll
get the total of both sales. You might add something to ensure that doesn't
happen (perhaps another field to count how many times that update number
appears for that rep)

F2=SUMPRODUCT(--(C2&" "&E2=$C$2:$C2&" "&$E$2:$E2))

copied down. Maybe add conditional formatting or data validation to
highlight any that appear more than once. Just a thought.

 

[haas786]

Say your table is in A1:E11 (w/headers in row 1). I assume you would want to
look up the sales based on RepID (instead of name, which may not be unique)

C2:C11 = Rep ID
D211 = Sales
E2:E11 = Update Number
G1 = Rep ID number you want to find

Try:
=SUMPRODUCT(--(C2:C11=G1),--(E2:E11=MAX((C2:C11=G1)*E2:E11)),D211)

although if the same update number is entered twice for the same rep, you'll
get the total of both sales. You might add something to ensure that doesn't
happen (perhaps another field to count how many times that update number
appears for that rep)

F2=SUMPRODUCT(--(C2&" "&E2=$C$2:$C2&" "&$E$2:$E2))

copied down. Maybe add conditional formatting or data validation to
highlight any that appear more than once. Just a thought.













- Show quoted text -

JMB,

Thanks for your help and effort, but that doesn't help solve my
problem. I'll repost again. Thanks again!