Search code examples
dataframeif-statementgoogle-sheetslambdagoogle-query-language

How can I convert one dataframe in google sheets into another format


The format the spreadsheet is currently in is.

Date Name
1/1/22 Joe schmoe
1/1/22 Jill will
1/2/22 Joe schmoe
1/2/22 Bob Flob

For each date. I have a variable number of names.

I would like the format to be

Name 1/1/22 1/2/22 1/3/22
Joe schmoe Present Present Present
Jill will Present absent absent
Bob Flob absent Present Present

How can I do this in google sheets? The first sheet is changing everyday. Thanks

I have tried to use a for loop and an if loop in spreadsheets and can't figure out what I need to do.


Solution

  • try:

    =QUERY(A:B, "select B,count(B) where B is not null group by B pivot A", 1)
    

    enter image description here

    or:

    =INDEX(LAMBDA(x, IF(x="", "absent", IF((x>0)*(x<40000), "present", x)))
     (QUERY(A:B, "select B,count(B) where B is not null group by B pivot A", 1)))
    

    enter image description here

    or:

    =INDEX(LAMBDA(x, IF(x="", "absent", IF((x>0)*(x<40000), "present", TEXT(x, "d/m/yy"))))
     (QUERY(A:B, "select B,count(B) where B is not null group by B pivot A", 1)))
    

    enter image description here