blank rows being returned, dates being returned twice

[Kanuck]

Hello, thank you in advance for any help.
Please see the attached doc to highlight the problems I am trying to solve.

I have a range of data, in the sheet marked data... and 3 sheets of heroes.
There is a date, and an activity.. and because of the way I have to pull my data, there is some blank rows in between some of the activities.

On the sheets of the heroes, I want to see the date the a hero did an activity.
The problems:

• I don't want the blank rows to be returned in column B for the heroes
• I want to see the date of an activity, but not if it occurs twice on the same day

I've tried a few approaches but what I have now is as close as I could get. Any help is appreciated.

Thank you,
kanuck

[Glenn Kennedy]

This returns the project:
=IFERROR(INDEX(Sheet1!C:C,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$100)/((Sheet1!$C$2:$C$100<>"")*(Sheet1!$B$2:$B$100=Sheet2!$C$5)),ROWS(C$9:C9))),"")

There are two options for retuning the date (I wasn't sure what you wanted). Take your pick and delete the unwanted column.

[Kanuck]

thank you!
that is really helpful. How would I make it so that if a project happened twice on the same day, it only appears once?

[Glenn Kennedy]

Date:
=IFERROR(INDEX(Sheet1!D:D,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$100)/((COUNTIFS($B$8:$B8,Sheet1!$C$2:$C$100,$A$8:$A8,Sheet1!$D$2:$D$100)=0)*(Sheet1!$B$2:$B$100=Sheet2!$B$5)*(Sheet1!$C$2:$C$100<>"")),1)),"")

Project:
=IFERROR(INDEX(Sheet1!C:C,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$100)/((COUNTIFS($B$8:$B8,Sheet1!$C$2:$C$100,$A$8:$A8,Sheet1!$D$2:$D$100)=0)*(Sheet1!$B$2:$B$100=Sheet2!$B$5)*(Sheet1!$C$2:$C$100<>"")),1)),"")

If you want the date to appear only once, you will still need the date column as shown in the sheet, but if needed, it can be hidden and another column used to return only the first instance of the date.

[Kanuck]

Thank you so much, that's exactly what was needed.

[Glenn Kennedy]

You're welcome and thanks for the rep.