Frequency Counting
 

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.

Frequency Counting
 

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

The first 20 rows shall show what you want.



--
Regards!
Stefi



€žJ.Scargill€� ezt Ã*rta:

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.

Frequency Counting
 

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

Frequency Counting
 

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??

"Stefi" wrote:

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

The first 20 rows shall show what you want.



--
Regards!
Stefi



€žJ.Scargill€� ezt Ã*rta:

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.

Frequency Counting
 

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??

"Stefi" wrote:

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

The first 20 rows shall show what you want.



--
Regards!
Stefi



€žJ.Scargill€� ezt Ã*rta:

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.

Frequency Counting
 

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$2:D2,Sheet 1!D2)1,"",ROW()))
B2:
=INDEX(Sheet1!D:D,SMALL(A:A,ROWS($1:1)))
C2:
=IF(ISERROR(B2),"",COUNTIF(Sheet1!D: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: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
Singapore
---

Frequency Counting
 

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:

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??

"Stefi" wrote:

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

The first 20 rows shall show what you want.



--
Regards!
Stefi



€žJ.Scargill€� ezt Ã*rta:

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.

Frequency Counting
 

Success! Thankyou very much Max.

"Max" wrote:

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$2:D2,Sheet 1!D2)1,"",ROW()))
B2:
=INDEX(Sheet1!D:D,SMALL(A:A,ROWS($1:1)))
C2:
=IF(ISERROR(B2),"",COUNTIF(Sheet1!D: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: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
Singapore
---

Frequency Counting
 

welcome, good to hear
--
Max
Singapore

"J.Scargill" wrote in message
...
Success! Thank you very much Max.