CODEWITHSUNDEEP
excelexcel-formulavba
msoi
msoi

Reputation: 13

How to group events in timeline matrix in Excel

I have two sheets: Events and Matrix.

In the Events Spreadsheet below I have persons name, event start day, event end day and info.

EVENTS

Events

In Matrix Spreadsheet have one column as names and the rest of the columns are timeline with rolling dates.

MATRIX

Matrix

Now I need to get event info from list of events for the persons timeline and group them. For example Stallones all events should be shown in Matrix. Is there anyway to do this without VBA?

Formula from matrix sheet:

=IF(AND(B$1>=VLOOKUP($A2;Events!$A$1:$D$6;2;FALSE);B$1<=VLOOKUP($A2;Events!$A$1:$D$6;3;FALSE));VLOOKUP($A2;Events!$A$1:$D$6;4;FALSE);"")

Any suggestions to do this more neatly would be highly appreciated too. Thanks!

Upvotes: 1

Views: 234

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You can try this array type formula:

=IFERROR(INDEX(Events!$D:$D,AGGREGATE(15,6,ROW(Events!$A$2:$A$6)/((Events!$B$2:$B$6<=B$1)*(Events!$C$2:$C$6>=B$1)*(Events!$A$2:$A$6=$A2)),1)),"")

enter image description here

Realize that a page full of these will slow down the calcs.

My Events page:

enter image description here

Upvotes: 1

Related Questions