Google-sheets – How to filter blanks in a table using ARRAYFORMULA

google sheetsgoogle-sheets-arrayformularegex

I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet:

I am filtering the non blank row-wise data. As data is coming from Google Form thus want to auto replicate this formula. Copy-down add-on and ARRAYFORMULA both are giving inaccurate results.

Best Answer

Please use the following formula

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                        "@")))

If on the other hand if you want to get just the first non-blank cells in each row, please use the following

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                       "@")),,1)

(I know they are a bit long... But they work)

Functions used:

Related Solutions

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

It appears that you are using regexmatch just to test string equality. This isn't necessary, equalities can be entered simply as Data!A:A = A1. You don't even need lower, because string comparison in filter is case-insensitive:

=filter(Data!A:R, Data!A:A = A1, Data!B:B = B, Data!C:C = C1)

(Also, there is no need for single quotes around sheet name when it does not contain spaces.)

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+

Best Answer

Related Solutions

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

Related Topic