ExcelBanter - Automatically filtering a range

You might try this approach:

Say your numbers are in A1 to A200.

Insert/use a helper Column B, and enter this formula in B1:

=COUNTIF($A$1:$A$200,A1)

Copy down to B200.

This gives you the number of times each number appears in the datalist.

Now, select A1 to B200 and sort them with primary sort on Column B, and
secondary sort on Column A.

You can use either ascending or descending, depending on your choice.

This gives you your numbers, grouped and sorted as to number of appearances
in the datalist.

Now, to create a unique list of you numbers, as they appear in this sorted
"frequency list", in C1 enter:
=B1
In C2, enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A$
1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(C$1:C1,$ A$1:$A$200&""),0)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy this formula down until you get a blank cell,
meaning your list of uniques is exhausted.

You now have your list of numbers, sorted by their frequency of appearance
in your original datalist.

You can now select the first 40 of these numbers, and right click in the
selection, and choose "Copy".

Navigate to your "other" sheet, right click in the starting cell, and choose
"Paste Special".
Click on "Values", and click on "Transpose",
Then <OK

You should now have your column of frequency sorted numbers displayed in a
row.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Winston Williams" wrote in
message ...
I have a two part question:
1. How do I automatically take data from one tab and sort the data based
on
the frequency the numbers are used and paste that data into another tab
showing a column with the data and the frequency the data occurred.

2. Automatically take the data generated above and copy and paste them
from
columns to rows based on the top 40 numbers generated above.

Also how can I set a data range for calculations that gets updated weekly
without having to go back and update the range.