Can I do this without a macro

[PaulGrogan]

Hi all, my first post here.

Lets say I have a table of 52 rows. One row for each week of the year
Each cell in the row is filled in on a weekly basis with a type o
fruit. The list of fruit is fixed using another datatable and dat
validation.

What I then need to do is for an area on the spreadsheet to show th
top 10 fruit for the year, based on the number of times it appears o
the list.

To get the number of each is easy, I use the COUNTIF function, so
have a part of the sheet that shows all the different types of frui
(say 30 of them), and how many times they appear on the list filled i
by the user.
i,e
Banana 0
Apple 4
Kiwi 1
etc. etc.

To do this with a macro, I simply write the macro to copy the cel
range with the counted occurances, sort it by the number column, an
then copy the top 10 results to my output table.

But, I want to avoid using a macro if possible, but cant think how t
do this otherwise.

Thanks in advance,

Pau

 

[papou]

Hi Paul
May be Pivot tables would be helpful in your case.

Cordially
Pascal

 

[CLR]

Hi Paul..........

Maybe the AutoFilter will give you what you want..........it will present
the "Top Ten".........

Vaya con Dios,
Chuck, CABGx3

 

[PaulGrogan]

Thanks for the quick reply. Never heard of pivot tables before. Had
quick play around and its confusing me a bit.

Anyone got any hints on getting started with pivot tables?

Thank

 

[Debra Dalgleish]

There are instructions and links for pivot tables here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[PaulGrogan]

Thanks for the replies up to now.

I created a pivot table from my data, and all I could get it to do wa
to count the number of occurances of each fruit. This I have alread
achieved by use of the COUNTIF function, but the pivot table, I hav
worked out how to get the table to autosort itself by selecting th
range and going into advanced options.

So, all that is left is for me to do is to get the pivot table to aut
refresh itself whenever the data is changed.

Can this be done

 

[Dave Peterson]

I don't think so.

But if your data is on one worksheet and the pivottable is on another, then you
could refresh it when ever you changed to that worksheet.

rightclick on the worksheet tab (with the pivottable) and click on select code.
Then paste this in:

Option Explicit
Private Sub Worksheet_Activate()

Dim myPT As PivotTable

For Each myPT In Me.PivotTables
myPT.RefreshTable
Next myPT

End Sub

If you have lots of pivottables in different sheets, you can get them all with a
line like:

ThisWorkbook.RefreshAll

 

[Ben Palmer]

I think I've found a way.

I have each weekly entry in Column A, starting in Cell A2.

The formula in Cell B2 is
=A2

The formula in Cell B3 is
=IF(COUNTIF($B$2:B2,A3)<1,A3,"")
then fill down to the last entry (Cell B53). This basically only
outputs the fruit to Column B if the corresponding entry in Column A
hasn't already been output in Column B.

The formula in Cell C2 is
=COUNTIF($A$2:$A$53,B2)
then fill down to Cell C53. This just outputs how many times each
fruit appears in the master list in Column A.

The formula in Cell D2 is
=C2

The formula in Cell D3 is
=IF(COUNTIF($D$22,C3)<1,C3+(E3/53),"")
this counts how many times the frequency in Column C occurs and
increments it by a fraction less than one so there are no duplicate
entries to rank later (see below).

In Column E, I have a list of increasing integers from 1 to 52 (Cells
E2 to E53). These are just used in Column D to increment the numbers
in Column D so there are no duplicated numbers in Column D.

The formula in Cell F2 is
=RANK(D2,$D$2:$D$53)
then fill down to Cell F53. This just ranks the entries in Column D.

The formula in Cell G2 is
=B2
then fill down to Cell G53. This just duplicates Column B for VLOOKUP
purposes.

The formula in Cell H2 is
=C2
then fill down to Cell H53. This just duplicates Column C for VLOOKUP
purposes.

Column I is blank.

Cells J2 to J11 have increasing integers 1 to 10, to identify the top
10 list.

The formula in Cell K2 is
=IF(VLOOKUP(J2,$F$2:$H$54,3,FALSE)=0,"",VLOOKUP(J2,$F$2:$H$54,3,FALSE))
then fill down to Cell K11. This returns the frequency of each of the
top ten fruits (unless then there is a zero frequency in which case it
returns blank).

The formula in Cell L2 is
=VLOOKUP(J2,$F$2:$G$54,2,FALSE)
then fill downto Cell L11. This returns the relevant fruit for the
top ten list.


Hope this helps.

Ben

 

[Llobid]

Paul...

This may be a convoluted solution, but if you look at the attache
picture, perhaps you can adapt what I have done to fit your needs.

In my example, I have three fruits: Apples, Bananas, and Pears...in th
array A16.

In column E, I have listed all of the individual fruits.

Column F has the Countif formula to count each fruit.

Column G has a formula to rank each fruit.

Column H then has a formula that lists the fruits that are ranked belo
3 (in my example)...yours would be "<10".

In my picture, I then sorted the array E1:H3 ascending by column G
Column H will then have the top ten fruits listed from the #1 seller t
the #10 seller.

I'm not an expert with Visual Basic, so I sometimes come up with wil
formulas to accomplish what I need

Attachment filename: ranking.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=61445