Automatically generate multiple row entries for a single entry in excel-CodePudding

I have a table of data for staff on various projects. I'd like to create a simple timeline for each staff member on each project. To explain this I have the following image where I have hardcoded the data from AB1 onwards to show how I'd like to present the data from the staffing allocation on the left.

I'm looking for a way to gather all the projects together for each employee in to AC and show their timeline with an associated colour based on their discipline - I imagine this part will have to be created with some formatting rules. Is it possible to create this with one function in AB2 or a combination of a function in AB2 and AD2 such that it automatically builds the table based on new entries being added to different projects in the left hand data?

CodePudding user response：

As per below screenshot formulas i have used. For coloring and hiding duplicate names you have to use conditional formatting.

E2=UNIQUE(A2:B17)
G1=TRANSPOSE(UNIQUE(SORT(EOMONTH(DATE(YEAR(C2:C17),MONTH(C2:C17),1),0))))
G2=XLOOKUP(1,($C$2:$C$17>=DATE(YEAR(G$1),MONTH(G$1),1))*($C$2:$C$17<=G$1)*($B$2:$B$17=$F2)*($A$2:$A$17=$E2),$A$2:$A$17,"")

CodePudding user response：

Try this in AB2:

=IF(A2:A20="","",IF(XLOOKUP(A2:A20,A2:A20,ROW(A2:A20))=ROW(A2:A20),A2:A20,""))

and this in AD2:

=IF((F2:F20<DATE(YEAR(AD1:AO1),MONTH(AD1:AO1) 1,1))*(H2:H20>=AD1:AO1),A2:A20,"")

and this for the conditional formatting rule:

=($F2:$F20<DATE(YEAR(AD$1:AO$1),MONTH(AD$1:AO$1) 1,1))*($H2:$H20>=AD$1:AO$1)*($D2:$D20="Programming")

Similarly for the other CF rules.