Large function and non-unique returned values
Posted by TCol on January 07, 2001 1:08 PM
I want to creat a top 10-type list from a large table I have.
Ideally, want to use functions only so that no physical sort
is required and spreadsheet updates in real-time fashion. A
macro would also suffice as an alternative.
Column A ("Company Name"), rows A1-A5 has names in it - lets say they are stocks.
ie IBM, MSFT, ORCL, CSCO and PALM.
Lets say Column D ("Shares"), rows D1-D5 represents the number of shares of each
i.e. 100, 250, 100, 100, 300
On a second sheet, I am capturing summary data extracted from
table above. I want to creat a list in descending order which
shows the number of shares, and the associated company.
Obviously the problem I am having is the non-unique number of
shares -- the functions I've attempted always return the same
company name for each of the 100-share lots. How can I
get around this - though I am not that experienced in EXCEL, I
have attempted to-date variations of Large, Rank, Match, Index,
Offset and VLookup. I think that one of the problems I've had
involves the type of arguments returned.
Any help would be very much appreciated - I've been stuck
on this for several weeks now!
Posted by Celia on January 07, 2001 6:02 PM
TCol
Here's one way :-
In cell E1 of Sheet1 (or the first row of some other blank column) enter the following array formula(Ctrl+Shift+Enter) and fill down to the last row of data :-
=RANK(D1,D$1:D$100)+COUNTIF(D$1:D1,D1)-1
For an explanation of this formula, have a look at
http://www.cpearson.com/excel/rank.htm
On Sheet2 enter the following two formulas and fill down to row 10.
In cell A1 :-
=INDEX(Sheet1!A$1:A$100,MATCH(ROW(),Sheet1!$E$1:$E$100,0))
In cell B1 :-
=INDEX(Sheet1!D$1:D$100,MATCH(ROW(),Sheet1!$E$1:$E$100,0))
Celia
Posted by Dave Hawley on January 07, 2001 10:23 PM
Hi Tcol
I would definately use a Pivot Table for this and not formulas.
They may seem a bit daunting at first, but stick with them and it WILL be worth it. I Have all my Stocks set up this way.
To create a very basic Pivot Table as you want try this:
1. Make sure your 2 columns have headings
2. Click any cell within your list. Go to Data>Pivot Table Report.
3. Select the very first option. Then click Next, Make sure your 2 columns you want are selected, including their headings. Click Next
4. Drag you "Company" heading to the ROW Field. Drag your "Number of Stocks" to the DATA field and to the ROW field.
5. Double click your "Number of Stocks" heading in the DATA field. Select "Sum" from the "Summerize By:" box. Take click around here and later on you can come back and set up all sorts of Statistics for your stocks. For now though click OK.
6. Double click on your "Company" heading and click "None" for Subtotals. Now click "Advanced" and select "Descending" and select "Sum of Number of Shares" or whatever you called this column. Now select "Automatic" for your "AutoShow Options" and set for top 10.
7. Double click on the "Number of Stocks" heading in the ROW field and do the same, but skip the sorting bit. Click Next
8. Choose a cell to start your Pivot Table and then click OK.
If you have got it right you'll be hooked on Pivot Tables. If not just yell and I'll send you a basic example.
If all is OK then save and have some trial and error by adding your own calculated fields, dragging headings about and much much more. The Undo will get you back in most cases, until you save.
There also some examples of Dynamic ranges on my Web site that are great for Pivot Tables. Click my link and then click "Dynamic Ranges"
OzGrid Business Applications
