excelunpivot

How to get for each non blank data entry in a range output in three columns containing the entry itself , the column header and the row header


I start with a range of data (a formula for instance combining the column/row header and/or "text" and or empty items).
the first row above the range is a column header (number or letter or empty). the first column left of data is a row header (descending numbers or letter or empty). All data, column/row headers are unique no repetitions (except the blanks).

As output I would like to get 3 columns 1 | 2 | 3 data | col header | row header

this data being only non blank entries (example of formula if(TRUE/FALSE,B1&A3,""). I add this because it seems that the formula output cannot be referenced always ? if B1&A3 as data entry then it should take it in the list if "" then it should evaluate the cell as blank and omit from the list.

An example table and output is depicted

  1. I made a helper column containing all data
  2. tried to used a Filter() to remove the blank entries in the column. resulted in a #SPILL
  3. Tried to get the header column and succeeded with CONCAT(REPT function
  4. tried to get the row header but did not succeed with INDEX MATCH

Solution

  • A particular solution, using TOCOLs ignore parameter:

    1 - Ignore blanks
    2 - Ignore errors
    
    =LET(
        data, B2:E7,
        col_header, A1:E1,
        row_header, A1:A7,
        result, VSTACK(
            {"Label", "Col", "Row"},
            HSTACK(
                TOCOL(data, 1, TRUE),
                TOCOL(IF(ISBLANK(data), NA(), INDEX(col_header, 1, COLUMN(data))), 2, TRUE),
                TOCOL(IF(ISBLANK(data), NA(), INDEX(row_header, ROW(data), 1)), 2, TRUE)
            )
        ),
        result
    )
    

    Formula and result