How can I Query Google Sheets data and Group the data where the data is names within a date column?

1
votes

Hoping someone can help me with this one. I have the following Google Sheets data in Sheet 1:

Data

I want to run Queries in another sheet so that when a date is selected the Query will return this:

After Query

The link to my working file is below:

Working File

I have started my query in cell D2 of the Other Attendances sheet. You will see that my query links to dropdowns, including a date range and I want all of the names who attended an event to be listed under the event and the date of the event rather than listed separately for each date. I also tried to create an IF statement if 'All' is selected but couldn't get that to work either.

I would appreciate help with a formula.

1
google-sheetsgoogle-sheets-query
@McChief - I'm assuming you intend to apply the formulae in 2nd sheet's B3, C3 cells (and so on...) and dynamically account for the "events attended" in B1, C1 (i.e. 2 cells above) and the "data of attendance" from 1 cell above (B2, C2...) - would that be right? – Sourabh Choraria
you say: "I want to run Queries in another sheet so that when a date is selected the Query will" but there are no formulae in your Sheet2 and from your brief question is not clear at all what exactly are you after. formula you wish to obtain as answer heavily depends on what exactly you want to accomplish and within each scenario, the formula would be significantly different. you want to query it by a single date? or date between two dates? all events? some events? date and event? ... from your 2nd picture, it looks like the output should be based on "Pre-Trip" event only and dispersed by date – player0
@Sourabh Choraria - sorry, I have been at a conference. I have now changed my question in the hope I'm am clearer. – McChief
@player0 - sorry, I have been at a conference. I have now changed my question in the hope I'm am clearer. – McChief
whats wrong with this formula? - i.stack.imgur.com/eEnxw.png isnt that the answer for your question? – player0

1 Answers

1
votes

I used two formulae in the Results sheet, cells A6 and B8.

The name data is messy, but you could work on the input to get it cleaned up.

Shared Sheet