# Create a list of categories from a 2D array

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.

• I don't think this can be done without hardcoding the maximum number of columns in the formula. Also, is the order of entries in the result required to be as shown (row by row)?
– user135384
Commented Dec 14, 2016 at 23:07
• @zaq, ordering doesn't matter. I was thinking I would sort the result anyway. But yeah, I'm afraid I can't do this as elegantly as I would like. Commented Dec 14, 2016 at 23:37

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`.

• There is a simpler way to get the concatenation of categories and subcategories: `IF(LEN(A1:C3),{A1:A3,A1:A3&"-"&B1:C3}&",",)`. This should work with open references too. Commented Mar 21, 2017 at 7:27

Given (sample input)

``````CategoryA   SubA1   SubA2
CategoryB
CategoryC   SubC1
``````

Then (expected result)

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

Assuming that the real categories and real subcateries doesn't include blank spaces, the following formula return the desired result

``````=ArrayFormula(
JOIN(
",",
SPLIT(
JOIN(
",",
QUERY(
TRANSPOSE(IF(LEN(A1:C3),{A1:A3,A1:A3&"-"&B1:C3}&",",)),
"",
10000000000
)
),
", ",
TRUE
)
)
)
``````

To adapt it to large input array, replace:

• `A1:C3` by the reference for the input array.
• `A1:A3` by the reference for the main categories array.
• `B1:C3` by the reference for the subcategories array.