CODEWITHSUNDEEP
excelunpivot
Abe
Abe

Reputation: 45

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

Upvotes: -1

Views: 58

Answers (2)

nkalvi
nkalvi

Reputation: 2614

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

Upvotes: 0

Michal
Michal

Reputation: 5848

You can do it using a formula, VBA or Power Query. Similar questions have have been asked many times already:

Unpivot an Excel matrix/pivot-table?

How to "unpivot" or "reverse pivot" in Excel?

enter image description here

Select your table, from the Data menu select "From Table/Range", load it into Power Query, select columns A, B, C, D, right click on them and select "Unpivot Columns".

enter image description here

The automatically generated M code is as follows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

If you want to use a formula, you could use a generic one such as (sourced from one of the links above) and adjust it to your taste and requirement.

=LET(dataRng,Table1[[A]:[D]],  rowAxis,Table1[Column1], colAxis,Table1[[#Headers],[A]:[D]],
   iCol,COLUMN(INDEX(rowAxis,1,1)),   amountCol,TOCOL(dataRng),  totalCells,COUNTA(amountCol),
          HSTACK(
              INDEX(rowAxis,
                     INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
                     BYCOL(INDEX(rowAxis,1,),  LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
              INDEX(colAxis,
                      SEQUENCE(1,ROWS(colAxis),1,1),
                      MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
               amountCol))

Upvotes: 0

Related Questions