Search code examples
excelmatchdynamic-tables

Count reference values using a dynamic second table

I have the following challenge: Every week i receive an excel file with upcoming tasks (table1) and would like to add up the time required for all morning and evening tasks separately per day.

Table 1: Planned tasks for the week

Monday Shift Tuesday Shift Wednesday Shift Thursday Shift Friday Shift
task 1 morning task 5 evening
task 6 evening
task 5 morning task 2 morning task 1 evening
task 4 evening
task 6 evening

What i did so far is separately count the different tasks and multiply them each with the time value stored in table 2 in a hidden sheet.

Table 2: Required time per task

Task Time
Task 1 1.0
Task 2 0.3
Task 3 0.8
Task 4 0.2
etc. etc.

As the references in the formula are hardcoded this has the major drawback of me having to modify the formula every time for each day when a new task is added:

=SUM(COUNTIFS($A$5:$A$200;"task 1";$B$5;"morning") * Table2!$A$1 + SUM(COUNTIFS($A$5:$A$200;"task 2";$B$5;"morning") * Table2!$A$2 SUM(COUNTIFS($A$5:$A$200;"task 3";$B$5;"morning")*Table2!$A$3 + .....

What i would like to achieve is a dynamic lookup in table2 for the keyword and corresponding value. That way the formula would no longer need to be modified and only keywords and times would need to be added to table2.

Unfortunately, I did not manage to find a suitable example of something comparable and didnt come up with something working myself (cannot use VBA or Python unfortunately).

I would really appreciate if someone could give me a hint on how to do this (or tell me if it is not possible in excel).

I tried to use the MATCH function, but i honestly got really confused by the syntax.

Solution

  • If you have 365, this can be done with the newer functions:

    =LET(
    d, taskTbl,
    tasks, WRAPROWS(TOCOL(d, 1), 2),
    morning, FILTER(tasks, CHOOSECOLS(tasks, 2) = "morning"),
    evening, FILTER(tasks, CHOOSECOLS(tasks, 2) = "evening"),
    morningTimes, XLOOKUP(CHOOSECOLS(morning, 1), timeTbl[Task], timeTbl[Time], 0),
    eveTimes, XLOOKUP(CHOOSECOLS(evening, 1), timeTbl[Task], timeTbl[Time], 0),
    VSTACK({"Morning Times", "Evening Time"}, HSTACK(SUM(morningTimes), SUM(eveTimes)))
)

    I Name'd the first table taskTbl and the second timeTbl. That way when you add tasks to either table, the formula will not have to be edited.

    I have also assumed that your tables only include the single week.

    For the table you show in your question, the result of this formula:

    enter image description here

    Note that tasks that do not exist in timeTbl will return 0 for the time so are not included in the totals.

    Algorithm

    • Convert the table to a single column
      • Then WRAPROWS to create a two column table
        enter image description here
    • Use the FILTER function to create two tables representing morning and evening
      enter image description here
    • XLOOKUP to obtain the relevant times
      (note the zero's for those tasks that don't exist in your Table 2)
      enter image description here
    • Return the sums of the morning and evening times separately

    Note:
    If you need to break it down into day of the week and hours for each shift, it becomes more complicated. You could extend the above algorithm to filter for each of those segments, but I would use Power Query as it will be easier to debug.

    You don't show how you would want to format a report so below is a starting point.

    To use Power Query, first select a cell in the timeTbl table and

    • Data => Get&Transform => from Table/Range.
    • In the PQ UI: Close and Load To and select connection only.

    Then select a cell in the taskTbl table.

    • Data => Get&Transform => from Table/Range
    • When the PQ Editor opens: Home => Advanced Editor
    • Paste the M Code below in place of what you see
    let

//Read in the data
    Source = Excel.CurrentWorkbook(){[Name="taskTbl"]}[Content],

//Create a List of all the column names
    colNames = Table.ColumnNames(Source),

//Split the table so as to create a list of Records
//  containing information as to Day, Task And shift
    #"Split Days" = 
        List.Combine(
            List.Accumulate(
                List.Numbers(0,5,2),
                {},
                (s,c)=> s & 
                    {List.Transform(
                        List.Transform(
                            List.Select(
                                List.Zip(Table.ToColumns(Table.SelectColumns(Source,List.Range(colNames,c,2)))), 
                            each List.NonNullCount(_)>0), 
                        each List.Combine({{colNames{c}}, _})),
                each Record.FromList(_,{"Day","Task","Shift"}))
                })),
            
//Convert the records to a Table
    #"Converted to Table" = Table.FromList(#"Split Days", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Day", "Task", "Shift"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column",{{"Day", type text}, {"Task", type text}, {"Shift", type text}}),

//Add the relevant times for each Task referring to the timeTble Table
    #"Add Time" = Table.AddColumn(#"Changed Type", "Time", (r)=> 
        Table.SelectRows(timeTbl, each Comparer.OrdinalIgnoreCase(r[Task],[Task])=0)[Time]{0}?, type nullable number),

//Group by Day and Shift and Sum the times
    #"Grouped Rows" = Table.Group(#"Add Time", {"Day", "Shift"}, {
        {"Time", each List.Sum([Time])??0, type nullable number}
        })
in
    #"Grouped Rows"

    Results
    enter image description here

    Of course, if you prefer an output like:
    enter image description here
    some minor code changes could easily be accomplished.

    In this table, the blanks represent shifts with no tasks at all.