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.
try:
=QUERY(A:B, "select B,count(B) where B is not null group by B pivot A", 1)
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)))
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)))