+ Reply to Thread
Results 1 to 25 of 25

I need to summarise data based multiple criteria

  1. #1
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    I need to summarise data based multiple criteria

    Hello all, I want to find to find a way to summarise and easily view the activities that are taking place each week in several areas on a construction project. Each activity has a type (13 possibilities), an area (52 possibilities) and has start and finish dates. The raw data is arranged so that all this information is contained on one row for each activity (around 6000 rows in total). Like this (UK style dates) -
    'Activity name' Activity type 1 Area 1 23/04/2019 05/05/2019
    'Activity name' Activity type 2 Area 1 08/05/2019 09/05/2019
    'Activity name' Activity type 1 Area 3 24/04/2019 28/04/2019
    'Activity name' Activity type 3 Area 2 29/04/2019 04/05/2019
    'Activity name' Activity type 5 Area 2 30/04/2019 03/05/2019
    'Activity name' Activity type 2 Area 3 30/04/2019 03/05/2019


    I'd like to be able to see what activity types are taking place in each area on a weekly basis similar to the layout here -
    Week start Week finish Area 1 Area 2 Area 3
    22/04/19 28/04/19 Activity type 1 Activity type 1
    29/04/19 05/05/19 Activity type 1 Activity type 3, Activity type 5 Activity type 2
    06/05/19 12/05/19 Activity type 2

    I could create a column for each activity type for each area and lookup if that type is taking place in that area for a given week and the summarise that into singles cells but this would require 676 columns (13 types x 52 areas) so I'd prefer a more efficient way if anyone can think of one?
    Thanks!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I need to summarise data based multiple criteria

    What do the 2 date columns in the first table represent?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Quote Originally Posted by Norie View Post
    What do the 2 date columns in the first table represent?
    They represent the start and finish dates of each activity. I need to organise the activities based on the area they take place in and the week they start and finish in. For example an activity of the type '1' takes place in area 1 and starts on 23/04/2019 (which falls in the first week 22/04/2019 - 28/04/2019) and finishes on 05/05/2019 (which falls in the second week 29/04/2019 - 05/05/2019) so would appear in the area 1 column in week 1 and week 2.
    Hopefully that explains it clearly!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: I need to summarise data based multiple criteria

    Do you need the activity name?

  5. #5
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    It isn't particularly relevant to this, no. Knowing the activity type is the important thing.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    Hello bwelsher and Welcome to Excel Forum.
    The following partially works (doesn't include comma or line break in area 2 for week starting April 29th) with the sample provided in post #1, however after reading the description in the post I am not sure that it will be broadly applicable.
    Two helper columns are added to the data that provide the week number(s) involved and both are populated using: =WEEKNUM(D1,2)
    One helper column is added to the output and that column is populated using: =WEEKNUM(L2,2)
    The Activity Types are placed in the blue area of the output range using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Thanks, can this formula account for more than two activity types taking place in the same area in the same week?

    EDIT: ignore that, I see I can extend the formula to account for extra activity types
    Last edited by bwelsher; 05-24-2019 at 05:14 AM.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    You're Welcome and thank you for the feedback. If the issue is resolved please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  9. #9
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Quote Originally Posted by JeteMc View Post
    You're Welcome and thank you for the feedback. If the issue is resolved please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
    Currently the formula will list an activity type alongside a week if it starts or finishes in that week but is there a way to amend the formula so it will also list activities that start in an earlier week and end in a later week. I.e the activity is ongoing in that particular week

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    See if the following will help.
    1. Modify the formula in column G to read: =IF(WEEKNUM(E1,2)=F1,"",WEEKNUM(E1,2))
    2. Place the following formula to columns H:J =IF(OR(G1="",SUM(F1,1)>=$G1),"",SUM(F1,1))
    3. Modify the formula in columns N:P to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that in the date in E1 has been changed to 5/8/19 so that Activity 1 in Area 1 spans weeks 17 - 19.
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Thanks for the update. I presume that I'll need to iterate the formula in columns N:P to cover the maximum number of activities that could take place in an area each week?
    E.g. In week 1 if there are 3 activities of type 1, 2 activities of type 2 and 1 activity of type 3 taking place in area 1 then I'd need at least 6 iterations -
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    For some reason that formula returned a #VALUE error, however the following formula displays the expected result:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  13. #13
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Ah yes I didn't use the exact formula it was just intended as a slightly rough example to illustrate my question. What I was asking in simple terms was - if there are lots of activities in the same week will the formula need to be equally long to capture them all?

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    Yes, the formula will need to be equally long. Looking back at the hoped for output in post #1 it might be worth mentioning that the spaces in the formula could be replaced with CHAR(10)'s. The cells in columns N:P could then be formatted to text wrap.
    Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  15. #15
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Thanks that would help with the formatting.
    I have started to realise that the simplified dataset that I gave in my first post was actually quite misleading as to the scale of the data I am trying to summarise. Looking through the full dataset I can see that in some weeks there may be over 20 activities of the same type taking place in the same area. This would require a formula with more than 20 iterations (not even considering different activity types). If more than one activity of the same type is taking place in the same area in the same week I would actually not need to know about each one (which is something I should also have pointed out earlier), e.g. if in week 1 in area 1 there are 3 activities of type 1 and 4 activities of type 2 I would only actually need to see this -
    Start Finish Area 1
    xx xx Activity type 1, Activity type 2
    Instead of this -
    Start Finish Area 1
    xx xx Activity type 1, Activity type 1, Activity type 1, Activity type 2, Activity type 2, Activity type 2, Activity type 2,,,,,,,,,,,,,

    Sorry I wasn't clearer but I appreciate all your efforts in getting me to this point though and I do at least have a formula that works!

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    Perhaps it would be worth exploring a power query (which is a free add-in for the 2013 version according to office.support.com) and pivot table option.
    On sheet 2 the original table (along with the week numbers) is put into a table in 'Records' format using power query.
    A second table which assigns start and end dates for each week is populated using formulas and will automatically update when the last cell in column N is selected and the tab key pressed.
    Columns H:I are added to the power query table and populated using: =INDEX(tbl_WkNum[Week Start],MATCH([@[Week Num]],tbl_WkNum[Week Num],0))
    On sheet 3 a pivot table displays the activities that are taking place in an area during a week. Note that while the activity is only listed once per week, the number of times that it occurs in an area is displayed.
    When additions/changes are made to the table 1 on sheet 1 then the tbl_ActivityByWeek on sheet 2 needs to be refreshed (table tools tab) and the pivot table on sheet 3 also needs to be refreshed (PivotTable tools > Analyze).
    Let us know if you have any questions.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Power query is required for this to work I guess? If so I'll need to talk IT into letting me install it.
    So the data on sheet 2 is drawn from sheet 1 via power query initially and then the pivot table is refreshed to show new data? Sorry, my experience with queries and pivot tables is very limited.
    I'm confused by the 'Wk1, Wk2, etc' headings, what do these mean?

    Thanks!

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    Better yet see if IT will let you upgrade to the current version of Excel (2019) which comes standard with Power Query (also called Get & Transform).
    You would not necessarily have to have Power Query. You could produce the green/white table manually and work directly from there. If you don't have too much data, i.e. more than 1 Mb, and if it isn't sensitive, you may upload it and I'll apply the Wk# columns then transform it to the green/white table. From there you could append the green/white table as you would any other Excel table.
    The way I have the attachment (post# 16) set up you would enter data into the table on sheet 1, then refresh the green/white table on sheet 2, then refresh the pivot table.
    Wk1, Wk2 … just means week 1, week 2 … Wk1 is the first week that the activity is held in an area, Wk2 is the last week, and Wk3, Wk4... are the weeks between. The column headers could be changed so long as they are unique i.e. you could label them First Wk, Last Wk, 1st Middle, 2nd Middle...
    Let us know if you have any questions.

  19. #19
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    Thank you for the offer but it's rather a lot of data and I will need to repeat the process periodically so I'd better find a way to do it myself. Will the query create a line in the green/white table for every activity type in every area in every week but with columns H and I showing an error if that activity type does not take place in that area in the week?

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    Yes, the INDEX formula will display #N/A if there is not a week number in column G. You could wrap the INDEX formula in IFERROR, however that seems a waste of computational time as the green/white table is only produced so that the pivot table will draw data from it.
    Let us know if you have questions about using power query (once IT approves the install).

  21. #21
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    I've put in the request but I'm testing it on my own copy at the moment which is 2016. I'll be interested to see how it goes with the full dataset but so far everything is going well as I throw more data at it.
    Could you explain a bit more behind the functioning of the week table on sheet 2 and how it generates when tab is pressed please? Because my week numbers will be arbitrary I've actually amended the formula in column N so that week numbers are determined using a LOOKUP on a list of dates and week numbers on another sheet. It still seems to be wokring fine though!

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    The formula in column L adds a day to the date in column M in the row above. The exception is that in row 2 the date 4/22/2019 is "hard coded" for the Monday of the earliest week listed in the table on Sheet1.
    The formula in column M adds 6 days to the date in column L of the same row.
    The formula in column N displays Excel's week number based on the date in column L. The [return type] argument (2) is set to start the week on a Monday.
    Since all of the columns are populated by formulas, the table will automatically update when the table is expanded.
    I hope that helps.
    I don't fully understand the bit about arbitrary week numbers and how a LOOKUP based formula is functioning, however as long as it is working...
    Let us know if you have any questions or run into problems.

  23. #23
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    The formula in column L may be modified to read: =IF(ROW()=2,MIN(Table1[Start])-WEEKDAY(MIN(Table1[Start])-2),DATE(YEAR(M1),MONTH(M1),DAY(M1)+1))
    This will enable the formula to find the Monday of the earliest week listed in the table on Sheet1 without the date being "hard coded" into the formula.
    Let us know if you have any questions.

  24. #24
    Registered User
    Join Date
    05-17-2019
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    12

    Re: I need to summarise data based multiple criteria

    What I meant about arbitrary week numbers is that the work starts in mid-August of this year so that would be week 1. Thank you for the update to the formula and, indeed, for all your help.

  25. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: I need to summarise data based multiple criteria

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to summarise a data table with multiple filters
    By amoodi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2014, 08:58 AM
  2. Summarise data based on date
    By ks04 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-08-2013, 10:54 AM
  3. [SOLVED] Summarise data, matching two criteria
    By overbomb in forum Excel General
    Replies: 8
    Last Post: 03-15-2012, 12:08 PM
  4. Summarise data from multiple sheets based on dates
    By tanktata in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 02:54 PM
  5. Replies: 2
    Last Post: 09-22-2010, 10:48 AM
  6. Summarise data with multiple filters
    By mikerstevens in forum Excel General
    Replies: 4
    Last Post: 02-25-2010, 07:10 AM
  7. Summarise multiple sheets based on conditions
    By Lotte Torkilson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-06-2009, 05:31 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1