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

Or maybe do I want a FIND function with a nested LARGE?

"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
>

Assuming your non-unique is in Cell d1...
=MAX(IF(A1:A126=D1,B1:B126,""))
then press Ctrl+Shift+Enter

HTH

Die_Another_Day
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

On Mon, 10 Jul 2006 11:45:02 -0700, JLeoni
<(E-Mail Removed)> 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
__________________________