Returning the max cell from a row based on matching data in the column below

Question

Brand new to stackoverflow so apologies if anything in this message doesn't fit typical norms.

I have an events sheet (in Google Sheets) where each column includes an event title, date, event type, and then a list of names. See here.

I want to be able to do things.

1. Return the most recent event that someone has attended. So, e.g., I want to be able to create a formula that returns 1/23/2016 for Alice and 1/16/2016 for Kyle.
2. Return the total number of events someone has attended over certain periods of time. So, for example, I want to be able to return "2" for the number of events Sam has attended in January 2016. Ideally, I'd also like to do this by event type. So, e.g., I'd like to return "1" for the number of protests Sam has attended in January 2016, and "1" for the number of community events.

I've tried VLOOKUP, INDEX-MATCH combinations, and various array formulas, but have not had any luck.

Answer 1

I suggest you converting your data to table view:

Step 1

Add new sheet.

Add this formula

• into A2:

=TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(TRANSPOSE(Sheet1!B5:F)&",")),","))

• into B2:

=TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(Sheet1!B2:F2&",",Sheet1!B3:F3))),","))

• into C2:

=TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(Sheet1!B1:F1&",",Sheet1!B3:F3))),","))

• into D2:

=TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(Sheet1!B4:F4&",",Sheet1!B3:F3))),","))

Step 2

And now you'll have this nice table and may use formulas:

1. Query
2. Filter
3. Unique + Sumif + Countif
4. and others.

For example, this formula:

=ArrayFormula(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")),SORT(A2:D,1,0,2,0),{1,2,3},0))

will give the list of the most recent events that someone has attended:

and this formula will show names, monthes and count of events someone has attended:

=QUERY({A:D},"select Col1, month(Col2)+1, count(Col3) where Col1 <>'' group by Col1, month(Col2)+1 label month(Col2)+1 'month'")

and it's also possible to add event type into this query:

=QUERY({A:D},"select Col1, month(Col2)+1, Col4, count(Col3) where Col1 <>'' group by Col1, Col4, month(Col2)+1 label month(Col2)+1 'month'")