Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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)
share|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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .