Search code examples
excelexcel-formulapowerqueryarray-formulas

Table to summarise data into a spreadsheet

I have the following table in excel

[Base Date](https://i.sstatic.net/bqBWw1Ur.png)

I have tried a number of formulas to get what I want such as:

=IFERROR(INDEX($H$1:$Z$1,MATCH(TRUE,INDEX($H2:$Z2>0,0),0)),"")

This returns the number 6431 which is correct, but what I need is to calculate a table which would look like something as follows:

[finished data set]

(https://i.sstatic.net/f55WLAP6.png)

There could be zero, one or multiple values in each row, all adding up to the total in Column D.

Solution

  • Highlight your table and under "data" in the ribbon select "From Table/Range" selecting that your data has headers.

    Remove the columns you don't need.

    Once the Power Query window opens, highlight the columns you want on their side, then in the "Transform" tab of the ribbon select "Unpivot Columns". This will make the headers their own column, and the values another column. From here, it should remove the null values, but if it doesn't you can filter those out at this step.

    If you want them summarized for duplicates, select Group By otherwise you can Close and Load and it should give you what you need.