Formula for counting frequency then sorting

[J.Scargill]

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.

 

[Glenn]

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.

 

[Pierre]

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

 

[Glenn]

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 here:

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

 

[J.Scargill]

Glenn, that is fabulous. My first time with Pivot Tables and am very
impressed. Thanks for your thorough reply.

 

[Glenn]

You're welcome! Glad I could help.