Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the last value entered for an entity
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the last value entered for an entity
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 D2:D11 = 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)),D2:D11) 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. " wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the last value entered for an entity
On Jul 13, 6:26 pm, JMB wrote:
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 D2:D11 = 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)),D2:D11) 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. " wrote: 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!- Hide quoted text - - Show quoted text - JMB, Thanks for your help and effort, but that doesn't help solve my problem. I'll repost again. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA | Excel Worksheet Functions | |||
Export Excel File as a Complete Entity | Excel Programming | |||
Finding total via entered text | Excel Worksheet Functions | |||
Finding the last row entered | Excel Worksheet Functions | |||
Finding the last entered value in a column | Excel Programming |