Hi,
I'm trying to construct a table that shows what project was being undertaken at a particular time by an individual. My input table comes from a system that tells me the individual name, the project they were working on and the time they started and finished. So where an individual has been working on multiple projects they will have multiple entries against their name.
I've split the input data into 5 min slots and created a sheet called Calculator that now shows their projects in column A, their names in column B and the times along row 1 and populates the project they were working on in the appropriate cell. Where they weren't working on a project, these cells have a 0 in them (though could easily be changed to a "" or 1 if it helps). At this stage, a person still appears multiple times in column B and projects occur multiple times in column A, however a person cannot be working on 2 projects at the same time.
I then want to create a table called Re-Calc showing everyone who was working on a specific day and what they were working on (with a view to colour coding the different projects). I've used
to generate a unique list of people in column A and I have the times along the top in row 1.
So I want to write a formula to find where the person's name matches the values in Calculator!column B, the time in row 1 matches the times in Calculator!row 1 and then return the first non-blank entry from the corresponding table. However I'm struggling with the Match function as it only finds the 1st match with the person's name. I've being struggling with this for a while now so think I've started going off on a tangent and therefore need help!
The closest I've got is the below:-
entered as an array formula, but it just finds the top line of data from Calculator. I guess this is logical as
(MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0) its finding the presence of the person's name, not a specific row so I'm now thinking I've gone about this in the wrong way!
Any help appreciated!
I'm trying to construct a table that shows what project was being undertaken at a particular time by an individual. My input table comes from a system that tells me the individual name, the project they were working on and the time they started and finished. So where an individual has been working on multiple projects they will have multiple entries against their name.
I've split the input data into 5 min slots and created a sheet called Calculator that now shows their projects in column A, their names in column B and the times along row 1 and populates the project they were working on in the appropriate cell. Where they weren't working on a project, these cells have a 0 in them (though could easily be changed to a "" or 1 if it helps). At this stage, a person still appears multiple times in column B and projects occur multiple times in column A, however a person cannot be working on 2 projects at the same time.
I then want to create a table called Re-Calc showing everyone who was working on a specific day and what they were working on (with a view to colour coding the different projects). I've used
Code:
{=IFERROR(INDEX(Calculator!$B$2:$B$500, MATCH(0,COUNTIF($A$1:A1, Calculator!$B$2:$B$500), 0)),"")}So I want to write a formula to find where the person's name matches the values in Calculator!column B, the time in row 1 matches the times in Calculator!row 1 and then return the first non-blank entry from the corresponding table. However I'm struggling with the Match function as it only finds the 1st match with the person's name. I've being struggling with this for a while now so think I've started going off on a tangent and therefore need help!
The closest I've got is the below:-
Code:
=SMALL(
IF(
AND(MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0),
INDEX(Calculator!BT$2:BT$500,MATCH(FALSE,ISBLANK(Calculator!BT$2:BT$500),0))),Calculator!$BT$1:$BT$500),1)entered as an array formula, but it just finds the top line of data from Calculator. I guess this is logical as
(MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0) its finding the presence of the person's name, not a specific row so I'm now thinking I've gone about this in the wrong way!
Any help appreciated!
