LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Return Top Ranks For Certain Categories in Column

One relatively easy way to get there ..

Assuming source data starts in row2 down

Put in Z2: =IF(C2="","",C2+ROW()/10^10)
Leave Z1 empty

Put in AA2:
=IF(ROWS($1:1)COUNT($Z:$Z),"",INDEX(A:A,MATCH(SMA LL($Z:$Z,ROWS($1:1)),$Z:$Z,0)))
Copy AA2 to AC2. Select Z2:AC2, copy down to cover the max expected extent
of source data, eg down to AC200? Minimize/hide away col Z. Cols AA to AC
will return the required auto-sort of source cols A to C (in ascending order
by the ranks in col C). Lines with tied ranks will be returned in the same
relative order that they appear within the source.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SteveC" wrote:
ColA ColB Columnc Col Z Col AA Col Ab
Col Ac
Apples Ship 3 Apples 1
Apples Train
Apples Train 1 2
Apples Tree
Apples Tree 2 3
Apples Ship
Pears Ball 2 4
Apples Pen
Pears Rock 1
Oranges Toy 1
Apples Pen 4
Orange Mack 2
Pears Sack 3


Had data is in the first 3 columns.

By inputting text into cell Z1, I would like to return the rank, category
and name in the Columns AA, AB and AC in order of rank. What formulas do I
use in AA, AB, and AC? Thanks a lot. I know "Max" has something to do with
it.

Stevec

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
column chart with 3 categories martymi Charts and Charting in Excel 2 May 1st 07 07:29 PM
Create Stacked column of multiple values across categories Elynn Charts and Charting in Excel 3 November 12th 06 09:01 AM
Column Chart - X axis too thin because of MANY categories. Mike Charts and Charting in Excel 3 June 15th 06 01:18 PM
How to create column of ranks. [email protected] Excel Discussion (Misc queries) 2 December 18th 05 04:23 AM
how do I sort a column with several categories (ie red and blue) cheryl Excel Worksheet Functions 1 January 20th 05 03:20 AM


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"