So, I have a sheet named "Calendar" and another sheet called "Stats". Here's a sample of the "Calendar" sheet:
| F | G | H | I | J | K |
|---|---|---|---|---|---|
| 2023-01-27 | Fri | 11:30 PM | Family | Family Activity 1 | YYY |
| 2023-01-27 | Fri | 11:45 PM | Family | Family Activity 1 | YYY |
| 2023-01-28 | Sat | 12:00 AM | Family | Family Activity 1 | YYY |
| 2023-01-28 | Sat | 12:15 AM | Family | Family Activity 1 | XY |
| 2023-01-28 | Sat | 12:30 AM | Fun | Fun Activity 1 | ABC |
| 2023-01-28 | Sat | 12:45 AM | Fun | Fun Activity 1 | ABC |
| 2023-01-28 | Sat | 1:00 AM | Obligations | Obligations 1 | AAA |
| 2023-01-28 | Sat | 1:15 AM | Fun | Fun Activity 2 | ZZZ |
| 2023-01-28 | Sat | 1:30 AM | Fun | Fun Activity 2 | ZZZ |
| 2023-01-28 | Sat | 1:45 AM | Family | Family Activity 2 | MMM |
| 2023-01-28 | Sat | 2:00 AM | Family | Family Activity 2 | MMM |
Now, on the "Stats" sheet there's a date in cell B16. For this example, it's 2023-01-28.
What I want is that I can get the columns H, I, J, and K from "Calendar" where F equals the date specified in cell B16 of the "Stats" sheet.
The tricky part, where I'm having issues, is to only show the rows where the previous row isn't identical, resp. where I, J, and K aren't the exact same as the previous row, like this:
| H | I | J | K |
|---|---|---|---|
| 12:00 AM | Family | Family Activity 1 | YYY |
| 12:15 AM | Family | Family Activity 1 | XY |
| 12:30 AM | Fun | Fun Activity 1 | ABC |
| 1:00 AM | Obligations | Obligations 1 | AAA |
| 1:15 AM | Fun | Fun Activity 2 | ZZZ |
| 1:45 AM | Family | Family Activity 2 | MMM |
I'm not sure if it's comprehensive, if it isn't please let me know so I can clarify.
What I got so far is the following formula:
=QUERY(A:K,"select H,I,J,K where F = date '2023-01-28'")
This only works if I execute it in the "Calendar" sheet and the date isn't dependent of cell B16 of the "Stats" sheet. However, ideally I'd like place the formula into the "Stats" sheet.
CodePudding user response：
you can try:
=FILTER(Calendar!H2:K,Calendar!F2:F=B16,{"";LAMBDA(z,MAKEARRAY(COUNTA(z),1,LAMBDA(r,c,IF(INDEX(z,r)=INDEX(z,r-1),1,))))(INDEX(Calendar!F3:F&Calendar!I3:I&Calendar!J3:J&Calendar!K3:K))}<>1)
CodePudding user response：
If you can, you may add an auxiliary column in your raw data sheet. I'll say it's L, with this formula in L2:
=MAP(F2:F,I2:I,J2:J,K2:K,LAMBDA(fx,ix,jx,kx,IF(OR(fx<>OFFSET(fx,-1,0),ix<>OFFSET(ix,-1,0),jx<>OFFSET(jx,-1,0),kx<>OFFSET(kx,-1,0)),1,0)))
It checks if F,I,J and K are equal, and returns 1 or 0. Then you can do a QUERY like this:
=QUERY(A:L,"select H,I,J,K WHERE L = 1 AND F = date '"&TEXT(B16,"YYYY-MM-DD")&"'")
If you can't add the column you may do it like this joining all this in one formula:
=QUERY({Calendar!F:K,"";MAP(Calendar!F2:F,Calendar!I2:I,Calendar!J2:J,Calendar!K2:K,LAMBDA(fx,ix,jx,kx,IF(OR(fx<>OFFSET(fx,-1,0),ix<>OFFSET(ix,-1,0),jx<>OFFSET(jx,-1,0),kx<>OFFSET(kx,-1,0)),1,0)))},"select Col3,Col4,Col5,Col6 WHERE Col7 = 1 AND Col1 = date '"&TEXT(Stats!B16,"YYYY-MM-DD")&"'")
CodePudding user response：
date is just a number. try:
=QUERY(Calendar!A:K, "select H,I,J,K where F = "&B16*1, )