Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
Hi guys,
Need some help with this one. My worksheet looks a little like this; Col F Col X Barcode Round Number 14 1234123412341234 12341 15 8687686876868686 86876 16 7543275432754327 75432 17 2482482482482482 24824 18 8687686777777777 86876 The worksheet is a year-to-date summary of all claims submitted to my department from our clients (data starts at row 14). Various data is submitted but I am focussing on the worst performing rounds. The round number is formulated as the first 5 digits of the barcode. Every week, we add to this sheet with the previous weeks claims. By the end of they year we expect appx. 1400 rows of data. I want to have (on the same sheet) a table that extracts the most frequent round numbers that appear in Col X and also puts a count in the opposing cell. I want this to automatically update each week. Say, the 20 most frequent. Is this possible? I appreciate your time. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
J.Scargill wrote:
Hi guys, Need some help with this one. My worksheet looks a little like this; Col F Col X Barcode Round Number 14 1234123412341234 12341 15 8687686876868686 86876 16 7543275432754327 75432 17 2482482482482482 24824 18 8687686777777777 86876 The worksheet is a year-to-date summary of all claims submitted to my department from our clients (data starts at row 14). Various data is submitted but I am focussing on the worst performing rounds. The round number is formulated as the first 5 digits of the barcode. Every week, we add to this sheet with the previous weeks claims. By the end of they year we expect appx. 1400 rows of data. I want to have (on the same sheet) a table that extracts the most frequent round numbers that appear in Col X and also puts a count in the opposing cell. I want this to automatically update each week. Say, the 20 most frequent. Is this possible? I appreciate your time. Use a PivotTable. Put Round Number in the Row Fields and in Data Items. Make sure it says "Count of Round Number" for Data Items. Right-Click Round Number in the Row Fields header and select "Field Settings". Click Advanced and select Descending under AutoSort options, Count of Round Number under Using field, On under Top 10 AutoShow and Top 20 next two Show:. Remember that you need to refresh the PivotTable when new data is added. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
Glenn wrote:
J.Scargill wrote: Hi guys, Need some help with this one. My worksheet looks a little like this; Col F Col X Barcode Round Number 14 1234123412341234 12341 15 8687686876868686 86876 16 7543275432754327 75432 17 2482482482482482 24824 18 8687686777777777 86876 The worksheet is a year-to-date summary of all claims submitted to my department from our clients (data starts at row 14). Various data is submitted but I am focussing on the worst performing rounds. The round number is formulated as the first 5 digits of the barcode. Every week, we add to this sheet with the previous weeks claims. By the end of they year we expect appx. 1400 rows of data. I want to have (on the same sheet) a table that extracts the most frequent round numbers that appear in Col X and also puts a count in the opposing cell. I want this to automatically update each week. Say, the 20 most frequent. Is this possible? I appreciate your time. Use a PivotTable. Put Round Number in the Row Fields and in Data Items. Make sure it says "Count of Round Number" for Data Items. Right-Click Round Number in the Row Fields header and select "Field Settings". Click Advanced and select Descending under AutoSort options, Count of Round Number under Using field, On under Top 10 AutoShow and Top 20 next two Show:. Remember that you need to refresh the PivotTable when new data is added. And if you are not familiar with PivotTables, start he http://peltiertech.com/Excel/Pivots/pivotstart.htm |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
Glenn, that is fabulous. My first time with Pivot Tables and am very
impressed. Thanks for your thorough reply. "Glenn" wrote: Glenn wrote: J.Scargill wrote: Hi guys, Need some help with this one. My worksheet looks a little like this; Col F Col X Barcode Round Number 14 1234123412341234 12341 15 8687686876868686 86876 16 7543275432754327 75432 17 2482482482482482 24824 18 8687686777777777 86876 The worksheet is a year-to-date summary of all claims submitted to my department from our clients (data starts at row 14). Various data is submitted but I am focussing on the worst performing rounds. The round number is formulated as the first 5 digits of the barcode. Every week, we add to this sheet with the previous weeks claims. By the end of they year we expect appx. 1400 rows of data. I want to have (on the same sheet) a table that extracts the most frequent round numbers that appear in Col X and also puts a count in the opposing cell. I want this to automatically update each week. Say, the 20 most frequent. Is this possible? I appreciate your time. Use a PivotTable. Put Round Number in the Row Fields and in Data Items. Make sure it says "Count of Round Number" for Data Items. Right-Click Round Number in the Row Fields header and select "Field Settings". Click Advanced and select Descending under AutoSort options, Count of Round Number under Using field, On under Top 10 AutoShow and Top 20 next two Show:. Remember that you need to refresh the PivotTable when new data is added. And if you are not familiar with PivotTables, start he http://peltiertech.com/Excel/Pivots/pivotstart.htm . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
You're welcome! Glad I could help.
J.Scargill wrote: Glenn, that is fabulous. My first time with Pivot Tables and am very impressed. Thanks for your thorough reply. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for counting frequency then sorting
On Apr 7, 8:17*am, J.Scargill
wrote: Hi guys, Need some help with this one. My worksheet looks a little like this; * * * * * * * * Col F * * * * * * * * * * * *Col X * * * * * * * Barcode * * * * * * * *Round Number 14 * *1234123412341234 * * * * * 12341 15 * *8687686876868686 * * * * * 86876 16 * *7543275432754327 * * * * * 75432 17 * *2482482482482482 * * * * * 24824 18 * *8687686777777777 * * * * * 86876 The worksheet is a year-to-date summary of all claims submitted to my department from our clients (data starts at row 14). Various data is submitted but I am focussing on the worst performing rounds. The round number is formulated as the first 5 digits of the barcode. Every week, we add to this sheet with the previous weeks claims. By the end of they year we expect appx. 1400 rows of data. I want to have (on the same sheet) a table that extracts the most frequent round numbers that appear in Col X and also puts a count in the opposing cell. I want this to automatically update each week. Say, the 20 most frequent. Is this possible? I appreciate your time. Use a pivot table. Sort by total number-criteria, and restrict the list to the top 20. For examples on doing this, visit contextures.com. Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frequency Counting | Excel Worksheet Functions | |||
Sorting by Frequency? | Excel Worksheet Functions | |||
word frequency counting | Excel Discussion (Misc queries) | |||
Counting Numbers of Same Frequency | Excel Discussion (Misc queries) | |||
Sorting - Frequency distribution. | Excel Discussion (Misc queries) |