google sheets - Arrayformula with if condition - only hiding row values instead of not including them

Question

Here is my test sheet for solving this problem: https://docs.google.com/spreadsheets/d/1UnYxy2HXPqA5aBr2WLHVFccktOBEgde-Chly2jna11k/edit?usp=sharing

1st sheet is a manually entered dump/log of calls 2nd sheet is supposed to be a daily report where user can type the date needed to print

The formula I used is =ARRAYFORMULA(IF('Call Log Details'!A2:A=D1,{'Call Log Details'!A2:A,'Call Log Details'!B2:B,'Call Log Details'!D2:D,'Call Log Details'!N2:N,ARRAYFORMULA(IF('Call Log Details'!E2:E="",,"IN................: " & 'Call Log Details'!A2:A & " " & 'Call Log Details'!B2:B & CHAR(10) & "CALLER.....: " & 'Call Log Details'!E2:E & CHAR(10) & "TEL#..........: " & 'Call Log Details'!G2:G & CHAR(10) & "PATIENT.....: " & 'Call Log Details'!D2:D & CHAR(10) & "CALL TYPE: " & 'Call Log Details'!I2:I & CHAR(10)& CHAR(10) & "MESSAGE.: " & CHAR(10) & 'Call Log Details'!J2:J & CHAR(10) & CHAR(10) & "NOTES......: " & 'Call Log Details'!T2:T)),'Call Log Details'!P2:P},))

It is comparing the date values for the two sheets. I think the comparison is correctly identifying the values needed. The problem I think is how I setup the array, because for example if I put 2/11/21 it will show the rows for 2/11/21 but instead of putting it in the first row, it goes in the 2nd row. But if I put 3/1/21, it shows both rows of 3/1/21 but in rows 1 and 3 skipping row 2. It might be that it is just copying all rows and just hiding the values depending on dates.

What I am looking as a result is when if I put 2/11/21, then it will be in the first row and not including 3/1/21. While if I put 3/1/21, it will show both 3/1/21 values in the first 2 rows and not including 2/11/21.

question from:https://stackoverflow.com/questions/65907896/arrayformula-with-if-condition-only-hiding-row-values-instead-of-not-including

Answer 1

Have a look at GK-Printer Friendly tab in your sheet.

I've switched to a QUERY, and simplified things by adding a helper column in column Z of the Call Details tab (see my copy of it, GK-Call Details).

The formula is now:

=query('GK-Call Log Details'!A2:Z," 
   select A,B,D,N,Z,P where A = '"&D1&"' order by A,B,D",0)

In Call Details, column Z (my copy) I'm doing the merger of the notes fields, which greatly simplifies the final result. This column could be hidden in Call Details, or it could be moved to a different tab, so it shouldn't be an inconvenience.