First & Last

[Dominique Feteau]

I have a large sheet that is sorted by file # & then by state with a ton of
other insignificant data in other columns. What i need is the first & last
file # for a particular state. is it easier to use 2 formulas for each
state: begin (which i can figure out how to get) and end (a little more
difficult to get). or would a macro work better (i wouldnt even know where
to start).

Any ideas?

[Gary''s Student]

If first and last really correspond to min and max, then the simplest thing
to use is a Pivot Table Report. It can generate a table by state that give
the min and max value for each file number.
--
Gary''s Student


"Dominique Feteau" wrote:

> I have a large sheet that is sorted by file # & then by state with a ton of
> other insignificant data in other columns. What i need is the first & last
> file # for a particular state. is it easier to use 2 formulas for each
> state: begin (which i can figure out how to get) and end (a little more
> difficult to get). or would a macro work better (i wouldnt even know where
> to start).
>
> Any ideas?
>
>
>

[Dominique Feteau]

That won't work.

The summary functions available for a field depend on the type of data in
the field. For example, if a field contains text, you cannot use Sum, Min,
or Max, but you can use Count for that field.

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> If first and last really correspond to min and max, then the simplest
thing
> to use is a Pivot Table Report. It can generate a table by state that
give
> the min and max value for each file number.
> --
> Gary''s Student
>
>
> "Dominique Feteau" wrote:
>
> > I have a large sheet that is sorted by file # & then by state with a ton
of
> > other insignificant data in other columns. What i need is the first &
last
> > file # for a particular state. is it easier to use 2 formulas for each
> > state: begin (which i can figure out how to get) and end (a little more
> > difficult to get). or would a macro work better (i wouldnt even know
where
> > to start).
> >
> > Any ideas?
> >
> >
> >

[Jana]

Dominique: When you say 'first & last file #' what do you mean? Is
this just the first & last entries in your list with the same state, or
are they numbers where you want the lowest file number and the highest
file number?

[Jana]

I don't understand your response.

[Gary''s Student]

I am not certain I understand. Is a file # not just a number:1,2,3,...??
--
Gary''s Student


"Gary''s Student" wrote:

> If first and last really correspond to min and max, then the simplest thing
> to use is a Pivot Table Report. It can generate a table by state that give
> the min and max value for each file number.
> --
> Gary''s Student
>
>
> "Dominique Feteau" wrote:
>
> > I have a large sheet that is sorted by file # & then by state with a ton of
> > other insignificant data in other columns. What i need is the first & last
> > file # for a particular state. is it easier to use 2 formulas for each
> > state: begin (which i can figure out how to get) and end (a little more
> > difficult to get). or would a macro work better (i wouldnt even know where
> > to start).
> >
> > Any ideas?
> >
> >
> >

[Dominique Feteau]

I'm sorry. I should have been a little more specific. The File No is
comprised of a number and the initials of the state in the beginning For
example: GA000018. So supposing Georgia had 10 files, I'd need something to
tell me Beginnging: GA000018 ; End GA000028.

Does that help?

"Jana" <[email protected]> wrote in message
news:[email protected]...
> Dominique: When you say 'first & last file #' what do you mean? Is
> this just the first & last entries in your list with the same state, or
> are they numbers where you want the lowest file number and the highest
> file number?
>

[Jana]

Hmmm...don't know what happened to my previous post, still hasn't shown
up, so I will just repost

Dominique, that helps ALOT! For a quick, down & dirty solution, I
would add a column to your sheet and put this formula in the cells:

=VALUE(MID(A1,3,15))

Replace A1 with the appropriate cell reference to your File No field.
The Mid function strips off the 1st two letters of the File No text (in
your case, the 2 letter state abbreviation) and the Value function
converts the remaining text to a number. So GA000018 becomes 000018
and then becomes the number 18. Once you have the number, you can
implement Gary's Student's solution using a Pivot Table Report and the
min/max functions of your new column.

Hope that helps and good luck,
Jana

Dominique Feteau wrote:
> I'm sorry. I should have been a little more specific. The File No is
> comprised of a number and the initials of the state in the beginning For
> example: GA000018. So supposing Georgia had 10 files, I'd need something to
> tell me Beginnging: GA000018 ; End GA000028.
>
> Does that help?
>
> "Jana" <[email protected]> wrote in message
> news:[email protected]...
> > Dominique: When you say 'first & last file #' what do you mean? Is
> > this just the first & last entries in your list with the same state, or
> > are they numbers where you want the lowest file number and the highest
> > file number?
> >

[Jana]

Dominique:
That helps alot!!! Rather than trying to program this, it might be
easier to go this route.

You could add a column that contains only the number of your File No
field and then use the suggestion Gary's Student made using a Pivot
Table Report using that new column for your min and max file numbers.

To try it, add a new column to your spreadsheet and use the following
formula:
=VALUE(MID(A1,3,15))

Replace the A1 with the applicable cell reference and copy it down the
column. The Mid function strips off the first 2 characters (in your
case the state abbreviation) of the File No text and the Value function
converts the remaining characters into a number. So GA000018 becomes
the number 18. As long as your File No information is consistent, this
should work

Hope that helps and good luck!

[Jana]

Dominique:

Did our suggestions work for you? Just curious...

Jana