Create a list of categories from a 2D array

Question

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.

Answer 1

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.

Answer 2

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.