Sorting in same column by 2 critieria ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used sub-total function to compile some stats by country name. Instead of having sub-total line sitting at the bottom, I need to place it on the top of each country group. The country groups have to be sort in ascending order alphabetically.
e.g
Instead of having I nee
Canada Canada Tota
Canada Canad
Canada Total Canad
U.S. U.S. Tota
U.S. U.S
U.S. Total U.S

I wonder if the column can somehow be sorted twice, first by country group ascending; then within each group descending
Can someone tell me whether this can be done and how? Thank
J
 
Hi
AFAIK this can't be done with Excel's build-in functionality. But you
may try the following:
- add a helper column (let's say column B)
- enter the following formula in B1
=IF(ISNUMBER(FIND("Total",A1)),LEFT(A1,LEN(A1)-6),A1)
copy this down. This should show only the names (and should strip the
word 'Total')
- now sort first with column B (ascending) and as second sort criteria
with column A (descending)
 
All that you have to do to have your totals show *above* the various
countries is *uncheck* "SummaryBelowData", which is found at the bottom of
the "SubTotal" dialog window.

This is usually checked by default.

This will however, also place your GrandTotal at the top of the sheet, just
under the headers.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I used sub-total function to compile some stats by country name. Instead of
having sub-total line sitting at the bottom, I need to place it on the top
of each country group. The country groups have to be sort in ascending order
alphabetically.
e.g.
Instead of having I need
Canada Canada Total
Canada Canada
Canada Total Canada
U.S. U.S. Total
U.S. U.S.
U.S. Total U.S.

I wonder if the column can somehow be sorted twice, first by country group
ascending; then within each group descending.
Can someone tell me whether this can be done and how? Thanks
JC
 
I found that, in the dropdown box after choosing sub-total function, just un-check the last option "summary below data" will result with sub-total lines sitting on the top of each data group. However, your methodology of using a helper column is enlightening and very userful too. Thanks Frank

----- Frank Kabel wrote: ----

H
AFAIK this can't be done with Excel's build-in functionality. But yo
may try the following
- add a helper column (let's say column B
- enter the following formula in B
=IF(ISNUMBER(FIND("Total",A1)),LEFT(A1,LEN(A1)-6),A1
copy this down. This should show only the names (and should strip th
word 'Total'
- now sort first with column B (ascending) and as second sort criteri
with column A (descending

-
Regard
Frank Kabe
Frankfurt, German


JC wrote
 
Back
Top