So I added a couple of helping columns. Sheet 1 A:A added the project name next to each entry - you can use an Offset if it will ALWAYS be the same number of rows for each project - Then I inserted a row (D) to count the instance of Each name in the sheet :
=IF(C9="","",COUNTIF($C$8:$C9,$C9))
ON SHEET 2 : Added in column A:A a count (series) beside each line so Chris has 1-10, Ellie has 1-10 etc... You have to unmerge B & C (it is not a good Idea to merge cells in this kind of application - just resize them )
in column B
=IF(COUNTIFS('Sheet 1'!$C:$C,$B$8,'Sheet 1'!$D:$D,$A12)=0,"",INDEX('Sheet 1'!$A:$BD,MATCH(1,('Sheet 1'!$C:$C=$B$8)*('Sheet 1'!$D:$D=$A12),0),1))
This is an array formula.
Under each date
=IF(COUNTIFS('Sheet 1'!$C:$C,$B$8,'Sheet 1'!$D:$D,$A12)=0,"",INDEX('Sheet 1'!$A:$BD,MATCH(1,('Sheet 1'!$C:$C=$B$8)*('Sheet 1'!$D:$D=$A12),0),MATCH(D$11,'Sheet 1'!$A$7:$BD$7,0)))
also an array formula.
There may be an easier way, but this is how I would try it.
Edit: Cheers - just saw you are in England
Bookmarks