Group data using Google Sheets Query function

Question

I have the following data in a Google Sheet:

Data

I want to run the Query function in another sheet so that when a date is selected the Query will return all attendees who attended an event on a single date in one column like this:

After Query

My formula only returns one Attendee under each date when there could be 50 staff attending one event. I want all 50 in one column rather than 50 separate columns.

Below is a link to my data. Cell D2 of the 'Other Attendances' sheet is my Query function.

Data

I would appreciate help with a formula.

player0 · Accepted Answer · 2019-10-21 21:09:56Z

1

you can do this:

=ARRAYFORMULA(TRANSPOSE({QUERY('Form Responses 4'!B:D,
 "select B,C 
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"' 
    and B = '"&B4&"' 
  order by C asc", 1), 
 REGEXREPLACE(SPLIT(QUERY('Form Responses 4'!B:D,
 "select D
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"' 
    and B = '"&B4&"' 
  order by C asc", 1), ";"), "^, ", )}))

=ARRAYFORMULA(TRANSPOSE({QUERY('Form Responses 4'!B:D,
 "select B,C 
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"' 
    and B = '"&B4&"'"&
    IF(B3<>"all", "and D contains '"&B3&"'", )&" 
  order by C asc", 1), 
 REGEXREPLACE(SPLIT(QUERY('Form Responses 4'!B:D,
 "select D
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"' 
    and B = '"&B4&"'"&
    IF(B3<>"all", "and D contains '"&B3&"'", )&" 
  order by C asc", 1), ";"), "^, ", )}))

=ARRAYFORMULA(TRANSPOSE({QUERY('Form Responses 4'!B:D,
 "select B,C 
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"'"& 
    IF(B4<>"all", "and B = '"&B4&"'", )&
    IF(B3<>"all", "and D contains '"&B3&"'", )&" 
  order by C asc", 1), 
 REGEXREPLACE(SPLIT(QUERY('Form Responses 4'!B:D,
 "select D
  where C >= date '"&TEXT(B5, "yyyy-mm-dd")&"'
    and C <= date '"&TEXT(B6, "yyyy-mm-dd")&"'"& 
    IF(B4<>"all", "and B = '"&B4&"'", )&
    IF(B3<>"all", "and D contains '"&B3&"'", )&" 
  order by C asc", 1), ";"), "^, ", )}))

edited Oct 21, 2019 at 21:09

answered Oct 18, 2019 at 0:15

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

McChief Over a year ago

Thank you. Works well when selecting all Attendees but doesn't work if you select one Attendee. I see that you changed the source data from Form Responses 4! A:C rather than E:F

player0 Over a year ago

did you even try the 2nd formula?

McChief Over a year ago

Sorry - I didn't see that. That's perfect - thank you very much

McChief Over a year ago

I have now noticed that the "all" function doesn't work for the Event Attended dropdown.

Collectives™ on Stack Overflow

Group data using Google Sheets Query function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related