Frequency Counting

[J.Scargill]

Hi guys,
I have a worksheet with a running history of yearly data that is added to
each week.
I want to set up a count for the most frequent round number that appears in
the round column, have the total amount counted for that round and then have
the details inserted on the same sheet. Ideally, Id like a Top 20 rounds for
my report.
Sheet looks like this;

Col A Col B Col C Col D
Code Depot Name Round
1234 57 Leeds 8754
4567 59 London 8234
1414 61 Belfast 8141
1245 57 Leeds 8754
1235 57 Leeds 8754
1423 61 Belfast 8141

So ideally I want the number 8754 to appear in my list with the freq 3 next
to it and so on.
Is this possible?
Many thanks in advance.
James.

 

[Stefi]

1. Create a unique list of Round numbers in, say, column F:
select column D> Data>Filter>Advanced Filter>check Copy to another location,
enter $F$1 in Copy to field (click on cell F1 when cursor is in this field),
check Unique records only>OK
2. Enter Frequency in cell G1 and enter formula =COUNTIF(D;F2) in G2 and
fill it down to the end of the unique list!
3. Select Column G>Copy>Pastespecial/Values (overwrite formulae with values)
4. Select Columns F:G>Data>Sort>Sort by Frequency/Descending

The first 20 rows shall show what you want.



--
Regards!
Stefi



„J.Scargill†ezt írta:

 

[Bernd P]

Hello James,

If you would not mind to use a macro I suggest to use my UDF Pfreq:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[J.Scargill]

Hi Stefi,

Thanks for your response.
I was kind of hoping for something that would automatically update whenever
new round numbers were added to Col D. As far as I can tell, your suggestion
would have to be carried out every week (the data is added weekly to this
sheet) to keep a running total??

 

[J.Scargill]

Hi Stefi,

Thanks for your reply.
As far as I can see, going with your suggestion would mean having to copy &
paste the latest weeks round numbers into Col F every week and then follow
out the steps. Is this correct?
I was looking for something that would just populate and update itself as
soon as the latest weeks data has been dropped in to the worksheet.
My knowledge is fairly basic so I am not sure if what I am asking for is
possible or not??

 

[Max]

I was looking for something that would just populate and update itself as

soon as the latest weeks data has been dropped in to the worksheet

Try this formulas play which achieves it dynamically
Assume your source data as posted is in Sheet1, cols A to D, data from row 2
down
In another sheet, place in
A2:
=IF(Sheet1!D2="","",IF(COUNTIF(Sheet1!D$22,Sheet1!D2)>1,"",ROW()))
B2:
=INDEX(Sheet1!D,SMALL(A:A,ROWS($1:1)))
C2:
=IF(ISERROR(B2),"",COUNTIF(Sheet1!D,B2)-ROW()/10^10)
D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(B:B,MATCH(LARGE(C:C,ROWS($1:1)),C:C,0)))
E2:
=IF(D2="","",COUNTIF(Sheet1!D,D2))
Copy A2:E2 down to cover the max expected extent of source data, say down to
E100. Minimize/hide cols A to C. Col D will return the full list of "round"
in descending order by their frequency counts (the counts are returned in col
E). Read off the top xx as desired. Ties if any, will be returned in the same
relative order that they appear in the source. The set-up will be dynamic to
new data in the source. Success? celebrate it, hit the YES below

 

[Stefi]

If you record the steps I wrote in a macro then it'll do the job after some
minor editing! If you are not familiar with macros, tomorrow I'll make it.

--
Regards!
Stefi



„J.Scargill†ezt írta:

 

[J.Scargill]

Success! Thankyou very much Max.

Try this formulas play which achieves it dynamically
Assume your source data as posted is in Sheet1, cols A to D, data from row 2
down
In another sheet, place in
A2:
=IF(Sheet1!D2="","",IF(COUNTIF(Sheet1!D$22,Sheet1!D2)>1,"",ROW()))
B2:
=INDEX(Sheet1!D,SMALL(A:A,ROWS($1:1)))
C2:
=IF(ISERROR(B2),"",COUNTIF(Sheet1!D,B2)-ROW()/10^10)
D2:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(B:B,MATCH(LARGE(C:C,ROWS($1:1)),C:C,0)))
E2:
=IF(D2="","",COUNTIF(Sheet1!D,D2))
Copy A2:E2 down to cover the max expected extent of source data, say down to
E100. Minimize/hide cols A to C. Col D will return the full list of "round"
in descending order by their frequency counts (the counts are returned in col
E). Read off the top xx as desired. Ties if any, will be returned in the same
relative order that they appear in the source. The set-up will be dynamic to
new data in the source. Success? celebrate it, hit the YES below

 

[Max]

welcome, good to hear