De-pivot excel spreadsheet

Question

Using Excel 2003 Can i...

Given a spreadsheet of this layout with Permissions being Read = R , Write = W and No permission= X

  Users     Perm1    Perm2   Perm3 ...    ..... PermX
  User1       R        W        X  ...    .....   R 
  User2       X        W        X  ...    .....   W
  User3       R        R        X  ...    .....   X
  ....
  ....

Can it be flattened to this

  Users     Perms    Value
  User1     Perm1      R
  User1     Perm2      W
  User1     Perm3      X
  ....
  ....
  User1     PermX      R
  User2     Perm1      X
  User2     Perm2      W
  User2     Perm3      X
  ....
  ....
  User2     PermX      W
  User3     Perm1      R
  User3     Perm2      R
  User3     Perm3      X
  ....
  ....
  User3     PermX      X

Is there an easy way to do this?

Answer 1

I would use the Index function:

Lets say your data source is in sheet1 in A1:D20. Then in sheet2 use these formulas:

Column A: "Row": =1, =1, =1, =A1+1, drag down
Column B: "Column": =1, =2, =3, =A1, drag down
Column C: "User": =INDEX(Sheet1!$A$2:$A$20,A2)
Column D: "Perms": =INDEX(Sheet1!$B$1:$D$1,B2)
Column E: "Value": INDEX(Sheet1!$B$2:$D$20,A2,B2)

Which should end up looking like this:

Row Column User  Perm  Value 
1   1      User1 Perm1 R     
1   2      User1 Perm2 W     
1   3      User1 Perm3 X     
2   1      User2 Perm1 X     
2   2      User2 Perm2 W     
2   3      User2 Perm3 X     
3   1      User3 Perm1 R     
3   2      User3 Perm2 R     
3   3      User3 Perm3 X     

Hope this helps.

EDIT:

The first two columns are just designed to cycle through all possibilities of row column combination. Say you have 40 different permission types and 500 users:

Row Column
1   1
1   2
1   3
1   4
...
1   39
1   40
2   1
2   2
2   3
2   4
...
2   39
2   40
3   1
...
500 40

If you would like to use formulas you can drag all the way down for this you can do this:

Column A: "Count": =1, =A1+1
Column B: "Row": =INT((A1-1)/5)+1
Column C: "Column": =MOD(A1-1,40)+1  (where you change 40 to the number of permissions you have)
Column D: "User": =INDEX(Sheet1!$A$2:$A$20,B2)
Column E: "Perms": =INDEX(Sheet1!$B$1:$D$1,C2)
Column F: "Value": INDEX(Sheet1!$B$2:$D$20,B2,C2)

Answer 2

Okay I answered this question 6 years after it is asked... And I am using Excel 2013 now. As far as I understand, this method should apply to Excel 2007, 2010, 2013. Not sure if it works in Excel 2016 though.

First, open up PivotTable and PivotChart Wizard by AltDP. It is hidden since Excel 2007 and it can only be opened through the shortcut keys.

Select "Multiple consolidation ranges" and click next.

Select "Create a single page field for me" and click next.

Click on Range and select the range to be "de-pivoted" (e.g. Sheet1!$A$1:$D$4). Click Finish.

After closing the wizard, you will see a pivot table created like this:

Double-click on the "Grand Total" cell (i.e. E8 in my example) and you will get a "De-pivoted" table on a new sheet like this:

It is not a perfect solution but definitely comes handy without having to write any formula, and the best solution so-far to me.

Answer 3

I've written a table unpivot program in javascript. See here. Explaining the workflow:

1. Convert table to json dictionary using a fork of a "Mr.Data Converter" tool by Shan Carter. (wasn't allowed to post more links here)
2. Loop through the JSON to list the table linearly.

See this graphic for explanation