Google Sheets – Create a List of Categories from a 2D Array

google sheets

I have data like the following:

CategoryA SubA1 SubA2
CategoryB
CategoryC SubC1
...

I would like to avoid using scripts and hopefully only use one (albeit complex) formula to get an array like the following:

{"CategoryA", "CateogryA-SubA1", "CategoryA-SubA2", "CategoryB", "CategoryC", "CategoryC-SubC1", ...}

So far I have been able to get various pieces of this, but I don't know how to combine it all together. For example I can get {"CategoryA-SubA1", "CategoryA-SubA2"} with ARRAYFORMULA(CONCAT(CONCAT(A1, "-"), FILTER(B1:1, NOT(B1:1 = "")))). I have also gotten this:

CategoryA-
CategoryB-
CategoryC-

using ARRAYFORMULA(CONCAT(FILTER(A1:A, NOT(A1:A="")), "-")).

The difficulty seems to come from dealing with a 2D array. I want to do a particular operation to each row, which involves doing a particular operation to each column in that row.

Best Answer

I think any formula will be limited by the number of columns or rows that it can handle. Here is one for 4 columns, A-D:

=iferror({filter(A1:A,len(A1:A)); filter(A1:A&"-"&B1:B, len(B1:B)); filter(A1:A&"-"&C1:C, len(C1:C)); filter(A1:A&"-"&D1:D, len(D1:D))})

It removes empty cells from each column, and prepends the content of A to the columns other than A. The iferror wrapper suppresses #N/A, which are returned when a filter has empty output. The output is a column; if you prefer a row, use transpose, and if you want a single-cell output, use join.

Related Solutions

Google-sheets – Using SUMPRODUCT with ARRAYFORMULA

An array solution could be:

=ArrayFormula(IF(LEN(A:A),COUNTIF(Sheet1!A:A&CHAR(9)&Sheet1!B:B,A:A&CHAR(9)&B:B),))

although it might be better to generate the unique counts in the QUERY itself:

=QUERY(Sheet1!A1:C10,"select A, B, count(C) where A != '' group by A, B order by A asc label count(C) ''",0)

This relies on there being a column C present in Sheet1 (it doesn't matter what is actually in column C). However, if wasn't viable having a column C, then it could be worked around.

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 – Using SUMPRODUCT with ARRAYFORMULA

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

Related Topic