CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
McChief
McChief

Reputation: 425

Group data using Google Sheets Query function

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.

Upvotes: 1

Views: 356

Answers (1)

player0
player0

Reputation: 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), ";"), "^, ", )}))

0

=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), ";"), "^, ", )}))

0

=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), ";"), "^, ", )}))

0

Upvotes: 1

Related Questions