Excel formula for availibility overview

[le00p]

Hey Community,

I am working in Excel trying to develop an overview for my company where we can see the scheduled projects and availibilities from our employees and ressources. 

We have a table with projects going on (Table name: Samples on the Sheet: ProjectsList). where we can see when the project is starting (Column I: Project Begin) and how long it is planned to go (Column A: Planned Time). In Column V and Y which ressource(machine) and which employee is scheduled for this project. There is always only one employee and one machine per project. 

The goal is to extract this data automatically into a new table where we have columns for each ressource and each employee. Whenever a new project is added to the project list, the information should go into this table.

I already tried with Vlookup with multiple criterias but did not find a working solution for that. Maybe there is somebody that can show me which formula can be used or give me other helpful tips :)

Thanks in advance and have a great day!

Leo

[#2]

@le00p 

=IFERROR(IFERROR(INDEX($C$3:$C$6,MATCH(1,($A9>=$B$3:$B$6)*($A9<=$F$3:$F$6)*(B$8=$E$3:$E$6),0)),INDEX($C$3:$C$6,MATCH(1,($A9>=$B$3:$B$6)*($A9<=$F$3:$F$6)*(B$8=$D$3:$D$6),0))),"")

You can try this formula along with a helper column which is in range F3:F6 in this example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is filled across range B9:H22.

This is the formula in cell F3: 

=B3+A3-1

[#3]

Hi @le00p 

With Get & Transform aka Power Query:

(Machine 2 doesn't appear above as it has no planned activity yet)

In attached file:

1. Put your data in the blue table sheet ProjectsLists
2. Switch to sheet Avail. Overview
3. Right-click in the green table > Refresh

[#4]

Thank you very much. I tried it with my 3000 rows of projects and it seems to work already pretty good.
Do you know how I can change this to Monday to Friday only? For example if a project is Starting on a Friday and the planned time is 3 days, for now it would say in the availibility overview: Project is from Friday to Sunday. But I want it to be on Friday Monday and Tuesday.

[#5]

Thank you very much. I will try that out if I can`t move on with the other tip.
But I appreciate the effort, thanks und Grüße ;)

[#6]

Hi @le00p 

Do you know how I can change this to Monday to Friday only?
Next question is likely going to be "how I can change this to exclude national holidays and company days off?" ;-))

Before adding >= 3K projects to the attached file could you check the the few records I used and let me know if they're accurate from your perspective?

[#7]

@le00p 

=LET(dates,

UNIQUE(SORT(DROP(REDUCE("",SEQUENCE(ROWS(Samples[Time Planned])),LAMBDA(x,y,VSTACK(x,TAKE(FILTER(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),(WEEKDAY(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),2)<6)),INDEX(Samples[Time Planned],y))))),1))),

lastdate,

SCAN("",SEQUENCE(ROWS(Samples[Time Planned])),LAMBDA(x,y,TAKE(VSTACK(x,TAKE(FILTER(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),(WEEKDAY(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),2)<6)),INDEX(Samples[Time Planned],y))),-1))),

emp_mach,

TOROW(SORT(UNIQUE(TOCOL(Samples[[Needed employee]:[Needed Ressource]])))),

HSTACK(VSTACK({""},dates),VSTACK(emp_mach,IFNA(MAKEARRAY(COUNTA(dates),COUNTA(emp_mach),LAMBDA(r,c,IFNA(INDEX(Samples[Project description],MATCH(1,(INDEX(dates,r)>=Samples[Project begin])*(INDEX(dates,r)<=lastdate)*(INDEX(emp_mach,c)=Samples[Needed employee]),0)),INDEX(Samples[Project description],MATCH(1,(INDEX(dates,r)>=Samples[Project begin])*(INDEX(dates,r)<=lastdate)*(INDEX(emp_mach,c)=Samples[Needed Ressource]),0))))),""))))

You are welcome. If you work with Office 365 or Excel for the web you can use this formula which excludes saturdays and sundays and dynamically spills the results according to the entries in the dynamic table.

[#8]

Thank you so much Oliver, I will try that out :)

[#9]

Yes, they are.