I have a worksheet that has aproximately 1500 rows. The information is
consistant down each column. Column C is populated with category numbers.
The spreadsheet is sorted by this column. There are approximately 75
different category numbers. Column D is populated with route numbers and
column F is populated with sales results. My question is this. How can I
rank each route number on sales, by category? I could physically define the
range of each category, but if I add or remove lines from a particular
category, my ranges won't work. Is this an array formula begging to be used?
--
Don Rountree

Don
Have you tried subsorting by column D then Subtotalling?

BTW Can your branch of the family not spell. Rowntree has a 'w' <g
Steve


"Don" wrote in message
...
I have a worksheet that has aproximately 1500 rows. The information is
consistant down each column. Column C is populated with category numbers.
The spreadsheet is sorted by this column. There are approximately 75
different category numbers. Column D is populated with route numbers and
column F is populated with sales results. My question is this. How can I
rank each route number on sales, by category? I could physically define
the
range of each category, but if I add or remove lines from a particular
category, my ranges won't work. Is this an array formula begging to be
used?
--
Don Rountree

You have lost me. I want to rank each category set. I don't want to
subtotal. By the way, we spell just fine. There are multiple spellings of
this name. My family broke from British rule and Americanized the spelling.

"Steve" wrote:

Don
Have you tried subsorting by column D then Subtotalling?

BTW Can your branch of the family not spell. Rowntree has a 'w' <g
Steve


"Don" wrote in message
...
I have a worksheet that has aproximately 1500 rows. The information is
consistant down each column. Column C is populated with category numbers.
The spreadsheet is sorted by this column. There are approximately 75
different category numbers. Column D is populated with route numbers and
column F is populated with sales results. My question is this. How can I
rank each route number on sales, by category? I could physically define
the
range of each category, but if I add or remove lines from a particular
category, my ranges won't work. Is this an array formula begging to be
used?
--
Don Rountree

Hi Don

Back from a unwanted interruption - had to go and earn some money in my
regular work and get some sleep.

My impression of your problem was that you needed to rank total earning by
Route. Example

Category, Route, Amount, Rank
A, A, $500, 1
A, A, $500, 1
A, B, $900, 3
A, C, $950, 2
....

Does this summarise the problem? If so, sort, subtotal and Rank() should do
the trick.
Category, Route, Subtotal, Rank
A, A, $1000, 1
A, B, $900, 3
A, C, $950, 2


If there's more to the problem, please post extra detail.


In case you're not up with family history, our mythical ancester was found
under a Rowan tree. That makes us lazy spellers. My family didn't choose to
stay under British rule either but didn't feel a need to start a war over
it. We remain colonial but only in name. I guess we must have some sense of
tradition.

Steve






"Don" wrote in message
...
You have lost me. I want to rank each category set. I don't want to
subtotal. By the way, we spell just fine. There are multiple spellings
of
this name. My family broke from British rule and Americanized the
spelling.

"Steve" wrote:

Don
Have you tried subsorting by column D then Subtotalling?

BTW Can your branch of the family not spell. Rowntree has a 'w' <g
Steve


"Don" wrote in message
...
I have a worksheet that has aproximately 1500 rows. The information is
consistant down each column. Column C is populated with category
numbers.
The spreadsheet is sorted by this column. There are approximately 75
different category numbers. Column D is populated with route numbers
and
column F is populated with sales results. My question is this. How
can I
rank each route number on sales, by category? I could physically
define
the
range of each category, but if I add or remove lines from a particular
category, my ranges won't work. Is this an array formula begging to be
used?
--
Don Rountree