CODEWITHSUNDEEP
sortinggoogle-sheetspivottransposegoogle-sheets-query
Jurg Zbinden
Jurg Zbinden

Reputation: 131

Sort data in google spreadsheet

I have a google form linked to a google sheet. The google form let me choose a date and select names. These persons are clients of a meeting at this date. The google form gives me a date in the 1st column and a list of names separated by commas in the 2nd column. Eg

8.15.2020 John, Mike, Eva
8.20.2020 John, Eva, Gudrun, Pete

Now I want to sort this tab by names - 1st column list of names, 1st row list of meeting dates, an 'x' where the person joined the meetiing and save it in a new tab. Eg

           8.15.2020  8.20.2020
John          x           x 
Mike          x 
Eva           x           x
Gudrun                    x
Pete                      x

How can I solve this problem?

Upvotes: 0

Views: 50

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(
 IF(IFERROR(SPLIT(Sheet1!B1:B; ", "))="";;
 Sheet1!A1:A&"♦"&SPLIT(Sheet1!B1:B; ", "))); "♦"); 
 "select Col2,count(Col2) 
  where Col2 is not null 
  group by Col2
  pivot Col1"))

0

to get xes try:

=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY(SPLIT(FLATTEN(
 IF(IFERROR(SPLIT(Sheet1!B1:B; ", "))="";;
 Sheet1!A1:A&"♦"&SPLIT(Sheet1!B1:B; ", "))); "♦"); 
 "select Col2,count(Col2) 
  where Col2 is not null 
  group by Col2
  pivot Col1")); "^\d+$"; "x"))

enter image description here

Upvotes: 1

Related Questions