Lookup latest entry 1

[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. The list is
always increasing as are the updates for each salesperson. I don't
want to find the sum of he person's sales, but the latest sale number
based on the latest update. In this case, John's sales figure would be
89,000 because the it is associated with the latest update number
(which happens to be the max for him.) This would be the entry I'm
interested in. Again, 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 this? Is there a way to figure out the max of the
UpdateNumber based on code and then doing a Vlookup against that? What
do i have to do to 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!

 

[Don Guillett]

One way where col a has last name and col E has 3. Assuming you have dates
the 1,2,3 etc would not be necessary.
This is an ARRAY formula that must be entered using ctrl+shift+enter vs just
enter

=INDIRECT("d"&MAX((A1:A65="Miller")*ROW(E1:E65)))

 

[Bob Phillips]

=INDEX(Sheet1!D120,MAX(IF(Sheet1!C1:C20="CA23",ROW(Sheet1!E1:E20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Max]

... I would like to pull up the entry from above list for

John to reflect the latest sale (89,000).

Assuming source data as posted is in Sheet1, within A2:E100

In Sheet2,

The Last & Firstnames are listed in cols A and B, from row 1 down
eg, you have in A1:B1 : Miller, John

Put in C1, array-enter the formula by pressing CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=IF(COUNTA(A1:B1)<2,"",INDEX(Sheet1!D$2$100,MATCH(MAX(IF((Sheet1!A$2:A$100=A1)*(Sheet1!B$2:B$100=B1),Sheet1!E$2:E$100)),IF((Sheet1!A$2:A$100=A1)*(Sheet1!B$2:B$100=B1),Sheet1!E$2:E$100)),0))
Copy C1 down

---

 

[Roger Govier]

Hi

I entered the name Miller in F2. The array entered formula

=SUMPRODUCT((A2:A11&E2:E11=F2&MAX(IF(A2:A11=F2,E2:E11,"")))*D211)
returned 89000

To enter or edit an array formula, use Control Shift Enter (CSE) not
just Enter.
Do not type the curly braces { } yourself, if you use CSE, Excel
will enter them for you.

 

[Bernd P]

Hello,

If your data resides in Sheet1A1:E11 (row1 being the title row), then

1. Enter into helper cell H1
=A1&", "&B1
and copy down to H11

2. goto Sheet2 and:

3. Enter into A1
=Sheet1!H1

4. Select A2:A11 and array-enter
=lfreq(Sheet1!H2:H11)

5. Enter into B1
Highest Update Number

6. Array-enter into B2
=MAX(IF(Sheet1!$A$2:$A$11&", "&Sheet1!$B$2:$B$11=A2,Sheet1!$E$2:$E
$11))
end copy down to B11

7. Enter into C1
Sales

8. Array-enter into C2
=INDEX(Sheet1!$D$2:$D$11,MATCH(A2&", "&B2,Sheet1!$A$2:$A$11&",
"&Sheet1!$B$2:$B$11&", "&Sheet1!$E$2:$E$11,))
and copy down to C11

You can find my UDF lfreq here:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd