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?

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?

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" wrote in message
...
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: 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?

I don't understand your response.

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?

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" wrote in message
oups.com...
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?

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" wrote in message
oups.com...
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?

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!

Dominique:

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

Jana