Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Create Unique List & Summarize All Associated Values

  1. #1
    Join Date
    30th January 2009
    Posts
    4

    Create Unique List & Summarize All Associated Values

    Hi,

    I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.

    My table looks like:



    In reality this list could be thousands of rows long.

    “Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).

    What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:



    Note that
    1) only venues that have run at least 2 meetings are shown in the output
    2) even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.


    In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.

    Have been battling with this with pivots, database functions, array formulae etc. for a long time. This forum looks to be full of experts – if anyone can help I would be most grateful!

    Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.

    Thanks,

    Adam
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

    Hi Adam,

    Welcome to OzGrid!!

    Try,

    In G5,

    =COUNTIF(B5:B16,G6)

    In K7 and copied down,

    =IF(ROWS($K$7:K7)<=$G$5,INDEX($A$1:$A$16,SMALL(IF($B$5:$B$16=$G$6,ROW($B$5:$B$16)),ROWS($K$7:K7))),"")

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    In L7 and copied down and across to M10

    =SUMPRODUCT(--($C$5:$C$16<=$G$6),--($A$5:$A$16=$K7),D$5:D$16)

    HTH

  3. #3
    Join Date
    30th January 2009
    Posts
    4

    Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

    Thanks so much Krishnakumar for your welcoming message and super quick answer. Your time is much appreciated.

    It seems to work perfectly. When I have time I will even try and understand exactly what it is doing!

    This forum might be the best site ever :-) Seems to have some useful tools around the site too.

    Thanks again.

    Adam

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

    Hi Adam,

    You are welcome !!

    Keep EXCELling !!

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: List All Unique Entries From Another List (and Associated Calculations) With Criteria

    You might consider a PivotTable.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create Unique List
    By mmejia0123 in forum Excel General
    Replies: 3
    Last Post: June 21st, 2008, 09:35
  2. Create Unique List From List With Duplicates
    By jerry8989 in forum Excel General
    Replies: 9
    Last Post: November 30th, 2007, 07:15
  3. Create A Unique List Of Values
    By spritchard in forum Excel General
    Replies: 1
    Last Post: October 5th, 2007, 05:03
  4. Replies: 10
    Last Post: November 28th, 2005, 07:44

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno