Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically filtering a range
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Excel 2003 Charts to a date range | Charts and Charting in Excel | |||
Filtering a dynamic range | Excel Worksheet Functions | |||
filtering on range of values | Excel Discussion (Misc queries) | |||
Advanced Filtering Extract Range Missing,etc | Excel Worksheet Functions | |||
Excel auto filtering to find a range of dates in a list | Excel Discussion (Misc queries) |