Backward pivot table sort of...

[Guest]

I have a spreadsheet similar to this which shows access right for several
depts:

Account Dept 1 Dept 2 Dept 3 Dept 4
Salaries READ READ READ READ
Supplies WRITE READ WRITE WRITE
DEPREC READ READ READ READ

I want to end up with:

Account Dept Access
Salaries 1 READ
Salaries 2 READ
Salaries 3 READ
Salaries 4 READ
Supplies 1 WRITE
Supplies 1 READ
Supplies 1 WRITE
Supplies 1 WRITE

etc....

Any suggestions????? THANKS!!!

 

[Guest]

You can use paste special to transpose rows and column.

If you copy Dept 1 to Dept 4
then Paste Special with rows and column transpose you get
Dept 1
Dept 2
Dept 3
Dept 4

You can the do a replace on Dept 1 to Dept 4 and replace "Dept " with ""
(nothing)

Salaries a supplies just can be copied

DEPREC can also be transposed.

 

[Debra Dalgleish]

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Before you start, change the Dept headings to numbers, or after you
unpivot you can use Edit>Replace to remove Dept from the Dept column.

 

[Guest]

I assume your source table is dynamic, i.e. varies in size, both rows and
columns, depending on the situation.
The following should work, provided you don't have duplicate entries under
the Account heading:-

1. Assume your source table has its top left cell (Account) in H3.
2. Two cells (anywhere) are used to hold the size of your source table
(assumes nothing else in the same rows and columns as the table):
column count in say F1, put in formula =COUNTA(H:H)
row count in say F2, put in formula =COUNTA(3:3)

3. Assume your result table has its headings in B12-D12.
4. Put the following formulas in the first row under each heading, and copy
them down until the table is filled.
Account:
=INDEX(OFFSET($H$3,1,,$F$1-1),INT((ROW(C12)-ROW($C$13)+1)/($F$2-1))+1)
Dept: =IF(B12<>B13,1,C12+1)
Access:
=INDEX(OFFSET($H$3,1,1,$F$1-1,$F$2-1),MATCH(B13,OFFSET($H$3,1,,$F$1-1),0),C13)

You can include the COUNTA formulas in the other formulas if you need to,
but it makes them even more complex than they already are. Once in place you
can move any of the tables and formulas to suit, without re-entering the
formulas.

 

[Guest]

That worked great! Thank you so much for your response!!

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.