Prev: Populating Row with dates
Next: Return Value from a Range (depends on input to determind month)
From: Max on 31 Mar 2010 10:04
> 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,Sheet1!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
---
From: Stefi on 31 Mar 2010 10:37
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> 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 1 Apr 2010 04:16
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,Sheet1!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
> ---
From: Max on 1 Apr 2010 21:25
welcome, good to hear
--
Max
Singapore

"J.Scargill" <JScargill(a)discussions.microsoft.com> wrote in message
news:790B1246-2B16-43B0-9290-0317EAA73FD5(a)microsoft.com...
> Success! Thank you very much Max.


First  |  Prev  | 
Pages: 1 2
Prev: Populating Row with dates
Next: Return Value from a Range (depends on input to determind month)