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?


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.


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)
| improve this answer | |
  • I will try this, however for brevity to post, I left out the details there about 1000 users and about 40 such permissions. The data is being maintained in a spreadsheet and I need to load the data on a scheduled basis to a system that holds the data in the output format. Also can you explain what the first two columns do? Thanks for the rapid response. Nice to know that we are not alone! – ggonsalv Jul 19 '10 at 18:43
  • Sounds interesting. I will give it whirl. Been missing my math, MOD brings back memories to fuzzy to remember!! Funny considered I graduated with a Bachelors Of Science in math and a minor Comp Sci. – ggonsalv Jul 20 '10 at 5:09

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.

PivotTable and PivotChart Wizard - Step 1 of 3

Select "Multiple consolidation ranges" and click next.

PivotTable and PivotChart Wizard - Step 2a of 3

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

PivotTable and PivotChart Wizard - Step 2b of 3

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: enter image description here

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:

enter image description here

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

| improve this answer | |

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.