+ Reply to Thread
Results 1 to 12 of 12

Summary creation from source data

  1. #1
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Summary creation from source data

    Hi guys,

    I have a spreadsheet in which there are multiple users in Column A (which I update almost monthly). Column B shows the period during which they can work in a week for example Sat - Sun means a user can work on Saturday and Sunday. Then the Columns K till Column AB shows the type of skills and "1" represents that they have that skill.

    I need to extract 2 things from this data which is what a user can do on a certain day. For example; if today is Monday and I want to know the employees that can stow and if it is Tuesday, I want to know the employees that can pick. I keep updating this list every now and then.

    And another thing that I want is to have a summary like how many stowers I have on Monday and how many Instructors on Thursday.

    I tried using PivotTable for these but its getting to complicated that way. If anyone could help in this or guide me on how to do this, I'd really appreciate it.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Summary creation from source data

    With AC1 is today or any specific date, from AC2 try:

    Please Login or Register  to view this content.
    Drag down

    Table AE1:AG8 is sum of stow/instructor number of employees those are ready for each week day:
    AF1
    Please Login or Register  to view this content.
    AG1:
    Please Login or Register  to view this content.
    Drag both down.

    Is it are you looking for?
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Re: Summary creation from source data

    Okay the second part where AE1:AG8 was helpful and I was able to utilize that part for various skills but the AC column part where you inserted the =TODAY() formula, that was not quite what I was looking for. Maybe I didn't elaborate it properly.

    I'll be updating this sheet after every few weeks, the source sheet. So I was trying to create a PivotTable or a sheet which will show me sort of a report for an entire week e.g. Monday I have XX number of employees who can stow, pick, instructor etc., Tuesday I have XX number of employees who can stow, pick, instructor etc. and for each day, like for Monday, I want a list of that XX number of employees so I can contact them for the particular day on which they can work. I have attached a sample sheet if that helps you understand.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Summary creation from source data

    It is a dynamic formula, with criteria is in B3 and B4

    In A5:

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Drag down
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Re: Summary creation from source data

    Okay so I tried this one method and it's working. I have linked the Date cell to the Date in the source sheet so updating it on the main sheet updates it there as well. However, I am trying to convert the source sheet into a "Table" so whenever I add more employees and their details, they are all incorporated into the Table and the formula on the main sheet picks up the data for that new employee as well. And also, whenever I add a new employee in the source data, will I have to update the formula again?

    Also, if its not too much trouble, could you explain the formula? Excel is really amazing and I'm always learning new things but I can't seem to understand this formula. It worked but it seems too complicated.

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

    Re: Summary creation from source data

    To convert the data on the Skills sheet to a table you'll need to change the contents of cell AC1 because column headers can not be formulas.
    You will also need to change the formula in AC2 and down to read: =INDEX($B2:$I2,WEEKDAY(TODAY()))
    Now remove the filters from the rest of the column headers by deselecting Filter on the Home tab or Data tab
    You can now convert the data to a table
    To test change the formula in cell C2 on the No. of Workers sheet to read: =SUMPRODUCT((Skills!$C$1:$I$1=B2)*(Skills!$K$2:$K$142)*(Skills!$C$2:$I$142="Can Work"))
    When you expand the table on the Skills sheet by selecting cell AC142 and pressing the tab key the formula in cell C2 on the No. of Workers sheet will automatically update to read:
    =SUMPRODUCT((Skills!$C$1:$I$1=B2)*(Skills!$K$2:$K$143)*(Skills!$C$2:$I$143="Can Work"))
    Let us know if you have any questions.
    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
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Re: Summary creation from source data

    So I've prepared the final version with your help and inserted a few charts as well to better portray the source data. However, just to test the "list of workers" sheet, I added 22 users by the same name "instructor" in Row 147 to Row 168 who can work on ALL days of the week and they all have the skill "Instructor". This addition in the source data should result in the "list of workers" having 22 user "instructor" but because of the same name or repetition (maybe), the list is showing only ONE "instructor" instead of 22.
    Attached Files Attached Files

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

    Re: Summary creation from source data

    Here is a copy of the file that adds 22 instructors (Instructor1...Instructor22) to the Skills sheet.
    Note that R147:R168 are populated with 1's and that the formula in column AB is copied to row 168.
    The names of the instructors now display on the List of Workers sheet.
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Re: Summary creation from source data

    Thank you for this but I think that maybe I did not explain myself clearly.

    The problem I am facing is that; suppose there are multiple users who happen to have the same name such as 22 multiple users with the same name "instructor", in that case, those multiple users with same name appear on the "List of Workers" sheet as only one "instructor" rather than 22 "instructor".

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

    Re: Summary creation from source data

    If you want to allow duplicate names to be displayed you may use the following formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: Paste the formula into cell J7 on the List of Workers sheet of the file attached to post #8 and then press the Enter key after which you may double click the fill handle to copy the formula down.
    Let us know if you have any questions.

  11. #11
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Re: Summary creation from source data

    Worked! Thank you so much!

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

    Re: Summary creation from source data

    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. [SOLVED] Pivot Table Creation maintaining source data format
    By egotrich in forum Excel General
    Replies: 1
    Last Post: 03-26-2018, 09:53 AM
  2. Replies: 12
    Last Post: 11-20-2017, 04:45 PM
  3. VBA Macro For Creation of Summary File from Workbooks
    By structo in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 09-16-2016, 12:25 PM
  4. [SOLVED] Sparklines - Creating Summary and want to source sparkline data from another table
    By AdamParker in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-08-2016, 08:49 AM
  5. Replies: 2
    Last Post: 07-28-2016, 10:02 AM
  6. Problems with Summary and Pivot Table creation macro
    By Handyann in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2015, 09:31 AM
  7. [SOLVED] Creating a summary list from source data - can you??
    By Marty in forum Excel General
    Replies: 1
    Last Post: 09-16-2005, 12:05 AM

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