On Mon, 10 Jul 2006 11:45:02 -0700, JLeoni
wrote:
I've looked through all the posts I could find back to 2005 and couldn't find
the exact answer to my question.
Setup:
I have created a VB form which populates a hidden data sheet.
Column A is account numbers - each unique
Column B is a Vendor name NOT UNIQUE
Problem:
I need to use the LARGE function to find the LAST number given to each of
the vendors
So what I think I'm looking for is a nested IF statement in a LARGE or the
other way around. Not quiet sure though how to formulate this.
Thanks in advance I know you all always come up with the answer
One way, and no doubt there are several others is as follows
Sort columns A:B with B as the first sort and A as the second - both
ascending.
Then in C1, assuming data is in A1:B12 enter
=INDIRECT("A"&MATCH(B1,$B$1:$B$12))
and copy down
the largest account number is repeated for each of the vendors
If you want to preserve the original order, then before the sort fill
a helper column with 1:12, do the sort including the helper column,
range value column B and resprt on the helper column.
HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________