Forum Discussion

sking_89's avatar
sking_89
Copper Contributor
Aug 05, 2024

Possible Array Formula?

I have a set of data: Rows = Job Titles Columns = Months Values = Budgeted hours within the month The hours are per month and per job title which means they can be quite high as we have more th...
BI & Data Analysis
Formulas and Functions
Macros and VBA
djclements's avatar
djclements
Silver Contributor
Aug 06, 2024

sking_89 Another possibility is to use the TOCOL-IFS method to unpivot the data and repeat the applicable rows:

 

=LET(
    proj,  BudgetData!B1,
    clnt,  BudgetData!B2,
    table, BudgetData!A4:G10,
    vals,  DROP(table, 1, 1),
    test1, vals<>0,
    jobs,  TOCOL(IFS(test1, TAKE(DROP(table, 1),, 1)), 2, 1),
    dates, TOCOL(IFS(test1, DROP(TAKE(table, 1),, 1)), 2, 1),
    hours, TOCOL(IFS(test1, vals), 2, 1),
    days,  ROUNDUP(hours/24, 0),
    cols,  SEQUENCE(, MAX(days)),
    offs,  cols-1,
    test2, days>=cols,
    CHOOSE(
        SEQUENCE(, 6),
        proj,
        clnt,
        TOCOL(IFS(test2, jobs), 2) & " - " & proj,
        TOCOL(IFS(test2, dates+offs), 2),
        1/1440,
        TOCOL(IFS(days=cols, hours-offs*24, days>cols, 24), 2)
    )
)

 

Adjust the range references as needed. Also, depending on how your attendance software interprets the data, you may still run into issues because technically 12:01 AM + 24 hours would be 12:01 AM the next day.

 

Please see the attached sample workbook, which also contains an additional variation...

Resources