Prev: Populating Row with dates
Next: Return Value from a Range (depends on input to determind month)
From: J.Scargill on 31 Mar 2010 04:29
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.
From: Stefi on 31 Mar 2010 05:50
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: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:

> 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.
From: Bernd P on 31 Mar 2010 07:42
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
From: J.Scargill on 31 Mar 2010 09:01
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> 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: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:
>
> > 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.
From: J.Scargill on 31 Mar 2010 09:11
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> 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: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:
>
> > 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.
 |  Next  |  Last
Pages: 1 2
Prev: Populating Row with dates
Next: Return Value from a Range (depends on input to determind month)