+ Reply to Thread
Results 1 to 12 of 12

Task counting

  1. #1
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Task counting

    Hi all,

    Everyday the department receives an email with the daily tasks. In the body of email (not any attached excels) there is a table with the following format:


    John Doe Kitchen / Cleaning

    Max the Driver Driving / Delivering / Reporting

    Charles Mg Reception

    Nick Pet Maintenance / Reporting

    Some people may have one task, while others may have two or more separated each time by a "/".

    When this table is copied to an excel (sheet2) it creates a table with two columns, the first being the names (A1 downwards) and the second being the tasks (B1 downwards). In sheet 1 there is a list with all the names on column A (A2 - A30) and all the tasks in row 1 (B1-P1). Length of both columns and rows might change as people might be hired/fired and tasks be removed / added. This will be known in advance though.

    So we have a grid of names and tasks (sheet1). The scope here is to create a monthly report of tasks that is adding the task table in sheet 2 and run a macro perhaps to auto count the tasks for each person and add a count (1,2 etc) to sheet1. Since tasks are daily and the report will be monthly, tasks will have to be added (summed up) in the grid from the second day onwards.


    Any help would be greatly appreciated.

    Thank you in advance
    Attached Files Attached Files
    Last edited by whitoulias; 03-27-2021 at 07:03 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    Two things.

    1. A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

    2. Please confrm the Excel version you are using (you joined 7 years ago and are showing Excel 2007... is that still the case?).
    Glenn



  3. #3
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Task counting

    Hi Glenn,

    You are right about everything...

    Excel version is 2010 and i have attached a small sample.

    Hope this helps.

    Thank you

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    Thanks. In your sample, the names are in the same order in BOTH sheets. Is that the case in your real data?

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    If they are, use this:

    =IFERROR(SUM(IF(ISNUMBER(SEARCH(B$1,Sheet2!$B1)),1,"")),"")
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    If they are in a different order, use this:

    =IFERROR(SUM(IF(ISNUMBER(SEARCH(B$1,VLOOKUP($A2,Sheet2!$A:$B,2,FALSE))),1,"")),"")

    i forgot to say.... maintenance needs to be spelled the same way in both sheets...

  7. #7
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Task counting

    That works a treat Glenn but when i try to add more records in sheet 2, that is same names with other tasks, sheet 1 is not updated. Names and tasks are of the existing ones and they are spelled the same way. And yes the names are in a different order so i used the second code you posted.

    Thank you

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    Post an updated sheet, showing what you mean.

  9. #9
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Task counting

    Hope this helps.

    Thank you
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    OK. This is OK in my version of Excel.... However, I have lost a little confidence in Excel's compatability checker in recent days. Check it carefully in your version.

    =IFERROR(1/(1/SUM(IF(ISNUMBER(SEARCH(B$1,INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$A$1:$A$50)/(Sheet2!$A$1:$A$50=Sheet1!$A2),ROW($1:$100))))),1,""))),"")


    array entered in B2, then copeid across and down. The formulae in the sheet are enclosed within a pair of { }, and are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-19-2014
    Location
    Greece
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Task counting

    It works perfectly without the nesting.

    Thank you for your time.

    Really appreciated!!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Task counting

    Glad to help. thanks for the feedback.

+ 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. Replies: 0
    Last Post: 03-14-2021, 04:26 PM
  2. Replies: 4
    Last Post: 01-06-2020, 11:38 AM
  3. Pivot table summarizing time spent per task per day from a task log
    By Fattyfatfat Kid in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-29-2019, 08:38 PM
  4. [SOLVED] Pivot Chart: Task spend over time, +comparison w/ Task Budget
    By mike_302 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-28-2018, 04:21 PM
  5. Replies: 3
    Last Post: 05-20-2017, 09:58 AM
  6. export excel list of task in custom outlook task 2010.
    By maxseal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2014, 03:51 PM
  7. [SOLVED] IF contingent task closed, THEN change formatting of dependent task cell
    By tek_9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2012, 08:40 PM

Tags for this Thread

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