1

I am looking for a solution to expand an excel matrix as per the example below:

Source data:

        6-14        14-22         22-6
Peter     3           4             2 
Paul      4           4           
Percy     2           2  
Pauline               1             4

Eventual output:

Peter    6-14
Peter    6-14
Peter    6-14
Peter    14-22
Peter    14-22
Peter    14-22
Peter    14-22
Peter    22-6
Peter    22-6
Paul     6-14
Paul     6-14
Paul     6-14
Paul     6-14
Paul     14-22
.....

The idea being that i adjust the values in the matrix to increase/decrease the number of rows generated per person.

Does anyone have any idea how to do this as i am stumped....

Many thanks for any help.

Community
  • 1
  • 1
Andy
  • 19
  • 2
  • 1
    This is a typical case of `unpivot` and there are many solutions out there. Just google for them: https://blogs.office.com/2015/12/15/learn-how-to-unpivot-static-tables-in-excel-2016/ OR https://support.office.com/en-us/article/Unpivot-columns-Power-Query-0f7bad4b-9ea1-49c1-9d95-f588221c7098 OR http://superuser.com/questions/78439/is-it-possible-to-unpivot-or-reverse-pivot-in-excel OR http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal OR just google for more.... – Ralph Jun 27 '16 at 12:52
  • Hi. Thank you for the quick response. I looked at unpivot, but it only offers the expanding of matrix, it doesn't enable me to generate the correct number of lines per person. For instance i would only have one line "Peter 6-14 3" rather than x3 "Peter 6-14" – Andy Jun 27 '16 at 14:31
  • That's why you'd have to unpivot multiple times. Look at the above referenced Q&A again and you'll see that `unpivot` does just that: make out of 1 row multiple rows. – Ralph Jun 27 '16 at 14:54

0 Answers0