Re: Find largest alphanumeric value matching alpha criteria in dat
- From: Alison <Alison@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 3 Aug 2005 07:04:05 -0700
The example of the alphanumerics criteria for the lookup is something like
"yes Calif Print Ad UTLA" (this is really just an alpha example)
which serves as a description for several projects.
The alphanumerics that I referred to that I need to get the largest of could
be something like the project:
Calif 5068
which is larger than (and therefore more recent than) the project:
Calif 5037
etc.
However, I have no problem with it getting the value from another field
which is purely numeric, just
5068
etc.
I don't need to get it from the field with the "Calif" in the front of it.
Numeric only will be fine. But even then the VLOOKUP won't find anything
past the first one it sees.
"Biff" wrote:
> Hi!
>
> >(Instead, I need to look at all the ones that match and return a field from
> >the largest alphnumberic match).
>
> Can you post some examples of these "alpha-numerics" and how do you
> determine one is larger than another?
>
> Do you mean something like:
>
> 2001-10-B
> 2000-75-Z
>
> > So the answer would be the job number which might look something like
> > "5002"
>
> If 5002 is a real number:
>
> =MAX(IF(A1:A100="Calif Insert", B1:B100))
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "Alison" <Alison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:9414591E-D5D5-4288-86E8-097AB03317DE@xxxxxxxxxxxxxxxx
> >I have a template that sends info to a database. In that template, I need
> >to
> > put in a function that will use alphabetic information in a cell as my
> > criteria. It then should look in the database to find records that match
> > that criteria and then send back to me the largest match from a field of
> > the
> > record among the records that match the criteria.
> > I have tried several functions, the best of which were VLOOKUP and DMAX.
> > Neither was what I needed. VLOOKUP gives field from the first record of
> > the
> > list that matches the criteria. (Instead, I need to look at all the ones
> > that match and return a field from the largest alphnumberic match). DMAX
> > for
> > some reason doesn't work when the database is closed and it doesn't let me
> > specify criteria for a field.
> >
> > Here's an example: the template is used to enter project information for
> > several clients. Each of those clients may do similar projects. Of those
> > projects and within each client, several of the projects may be similar.
> > Let's say I have two projects and need to find the most recent that was
> > similar to another project. Projects have project numbers that go higher
> > as
> > they become more recent. In the template, I can have the information
> > "Calif
> > Insert" which represents the client name and project type, and want the
> > function to go to the database to find all the records that have "Calif
> > Insert" in a specified column. Then find me from another specified field
> > the
> > largest value in that field that matches "Client Insert" in the other
> > field.
> > So the answer would be the job number which might look something like
> > "5002"
> > which represents the most recent job (highest numbered job) that is also
> > described as "Calif Insert"
> > As I said, VLOOKUP was very close, but gave me the FIRST in the list that
> > matched the criteria "CLIENT NAME PROJECT" but not the highest. I guess
> > VLOOKUP doesn't have the ability to go past the first one it sees.
> > Sorting
> > the database is not an option.
> > I am desparate. Any help would be appreciated.
> > Thanks.
>
>
>
.
- References:
- Prev by Date: Re: Which function to use?
- Next by Date: RE: How do I calculate a future date in Excel?
- Previous by thread: Re: Find largest alphanumeric value matching alpha criteria in databas
- Next by thread: Re: Find largest alphanumeric value matching alpha criteria in dat
- Index(es):
Relevant Pages
|
