Reorganizing data in a different layout in Excel

Question

I would like to change the structure of a dataset so as to be able to extract information easily. Here is my example: I have 5 portfolios, each of them containing different number of shares of different products. The information is stored in the following table:

What I would like to do is to change the shape of the table into the following:

If I had few data, I would just apply a filter and exclude for each portfolio the "0" from the selection (and I would do this for each of them separately). Hence, I'm looking for a way to do this in an automatic way, without doing it manually. I'm not sure if there is a way to do it in Excel. I was thinking about the following SQL (pseudo-)code:

SELECT Portfolio_Name, Product_Name, Number_Shares 
FROM Table_Name 
GROUP BY Portfolio_Name

I think you will like this [Reverse Pivot](http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal). You can delete `0` lines after "flattening" the table. — Scott Holtzman, Feb 22 '16 at 21:00
Loop over the cells in the "value" range: if the cell value is >0 then use the cells `Row` and `Column` to extract the Portfolio and Product headers and add the three values to your "output" list. See the answer here: http://www.mrexcel.com/forum/excel-questions/701104-convert-matrix-worrk-sheet-flat-file.html — Tim Williams, Feb 22 '16 at 21:00

score 0 · Answer 1 · answered Feb 22 '16 at 21:07

Try a Pivot in the SQL code, something along the lines of this:

SELECT *
FROM (
    SELECT 
        Portfolio_Name, Product_Name, Number_Shares
    FROM Table_Name
) as s
PIVOT
(
    SUM(Number_shares)
    FOR [portfolio_name] IN (portfolio1, portfolio2, portfolio3, portfolio4, 
    portfolio5)
)AS pvt

Reorganizing data in a different layout in Excel

1 Answers1