Excel data matrix into a pivot

[FA Accountant]

Hi, I need some help to put the following data into an excel pivot so that I can look at any Oracle Role and see only those responsibilities that apply (ie where the X exists - I don't want to see any of blank cells_). Is there a way of doing this? A regular pivot does not seem to work.....thks!

[TABLE="width: 794"]
<colgroup><col width="265" style="width: 199pt; mso-width-source: userset; mso-width-alt: 9691;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <tbody>[TR]
[TD="width: 265, bgcolor: #A9D08E"]Oracle Role[/TD]
[TD="width: 86, bgcolor: yellow"]AP User [/TD]
[TD="width: 97, bgcolor: yellow"]AP Inquiry[/TD]
[TD="width: 98, bgcolor: yellow"]AP Manager[/TD]
[TD="width: 104, bgcolor: yellow"]GL Inquiry[/TD]
[TD="width: 102, bgcolor: yellow"]GL User[/TD]
[TD="width: 96, bgcolor: yellow"]FA Inquiry[/TD]
[TD="width: 102, bgcolor: yellow"]FA User[/TD]
[TD="width: 105, bgcolor: yellow"]FA Manager[/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role AP Team member[/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role AP Team Lead[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role Projects Team Member[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role Projects Team Lead[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role Fixed Asset Team Member[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #A9D08E"]Oracle Role Fixed Asset TeamLead[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Would this be an acceptable presentation

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Oracle Role​

[/td][td]

Attribute​

[/td][td]

Value​

[/td][/tr]


[tr][td]

2​

[/td][td]

Oracle Role AP Team member​

[/td][td]

AP User​

[/td][td]

X​

[/td][/tr]


[tr][td]

3​

[/td][td]

Oracle Role AP Team member​

[/td][td]

GL Inquiry​

[/td][td]

X​

[/td][/tr]


[tr][td]

4​

[/td][td]

Oracle Role AP Team Lead​

[/td][td]

AP Manager​

[/td][td]

X​

[/td][/tr]


[tr][td]

5​

[/td][td]

Oracle Role AP Team Lead​

[/td][td]

GL User​

[/td][td]

X​

[/td][/tr]


[tr][td]

6​

[/td][td]

Oracle Role Projects Team Member​

[/td][td]

AP Inquiry​

[/td][td]

X​

[/td][/tr]


[tr][td]

7​

[/td][td]

Oracle Role Projects Team Lead​

[/td][td]

GL User​

[/td][td]

X​

[/td][/tr]


[tr][td]

8​

[/td][td]

Oracle Role Projects Team Lead​

[/td][td]

FA Manager​

[/td][td]

X​

[/td][/tr]


[tr][td]

9​

[/td][td]

Oracle Role Fixed Asset Team Member​

[/td][td]

AP Inquiry​

[/td][td]

X​

[/td][/tr]


[tr][td]

10​

[/td][td]

Oracle Role Fixed Asset Team Member​

[/td][td]

GL User​

[/td][td]

X​

[/td][/tr]


[tr][td]

11​

[/td][td]

Oracle Role Fixed Asset Team Member​

[/td][td]

FA User​

[/td][td]

X​

[/td][/tr]


[tr][td]

12​

[/td][td]

Oracle Role Fixed Asset TeamLead​

[/td][td]

FA Manager​

[/td][td]

X​

[/td][/tr]
[/table]

If so, load to Power Query and Unpivot the columns.

 

[FA Accountant]

Thanks for quick reply Alan; this is not really what I'm after...ideally I want to see just one row for each Oracle Role and then the Attributes that apply to that Role, so AP Team Member with AP User & GL Inquiry only. It may not be possible...my objective is to review these roles and associate responsibilities / attributes easily

 

[alansidman]

Using a PIvot table after creating a normalized data as shown in my earlier post, would this work for you.

Data Range
[Table="class: grid"][tr][td] [/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

2​

[/td][td]

Oracle Role​

[/td][td]

Attribute​

[/td][/tr]


[tr][td]

3​

[/td][td]

Oracle Role AP Team Lead​

[/td][td]

​

[/td][/tr]


[tr][td]

4​

[/td][td]

​

[/td][td]

AP Manager​

[/td][/tr]


[tr][td]

5​

[/td][td]

​

[/td][td]

GL User​

[/td][/tr]


[tr][td]

6​

[/td][td]

Oracle Role AP Team member​

[/td][td]

​

[/td][/tr]


[tr][td]

7​

[/td][td]

​

[/td][td]

AP User​

[/td][/tr]


[tr][td]

8​

[/td][td]

​

[/td][td]

GL Inquiry​

[/td][/tr]


[tr][td]

9​

[/td][td]

Oracle Role Fixed Asset Team Member​

[/td][td]

​

[/td][/tr]


[tr][td]

10​

[/td][td]

​

[/td][td]

AP Inquiry​

[/td][/tr]


[tr][td]

11​

[/td][td]

​

[/td][td]

FA User​

[/td][/tr]


[tr][td]

12​

[/td][td]

​

[/td][td]

GL User​

[/td][/tr]


[tr][td]

13​

[/td][td]

Oracle Role Fixed Asset TeamLead​

[/td][td]

​

[/td][/tr]


[tr][td]

14​

[/td][td]

​

[/td][td]

FA Manager​

[/td][/tr]


[tr][td]

15​

[/td][td]

Oracle Role Projects Team Lead​

[/td][td]

​

[/td][/tr]


[tr][td]

16​

[/td][td]

​

[/td][td]

FA Manager​

[/td][/tr]


[tr][td]

17​

[/td][td]

​

[/td][td]

GL User​

[/td][/tr]


[tr][td]

18​

[/td][td]

Oracle Role Projects Team Member​

[/td][td]

​

[/td][/tr]


[tr][td]

19​

[/td][td]

​

[/td][td]

AP Inquiry​

[/td][/tr]
[/table]

 

[FA Accountant]

Hi Alan, yes this would work! Would you mind to advise how I can do this though? I'm not familiar with "normalized data"?. Thanks so much...

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Oracle Role​

[/TD]
[TD]

Attribute​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Oracle Role AP Team Lead​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

AP Manager​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

GL User​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Oracle Role AP Team member​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

AP User​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

GL Inquiry​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

Oracle Role Fixed Asset Team Member​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

AP Inquiry​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD]

FA User​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD]

GL User​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

Oracle Role Fixed Asset TeamLead​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD]

FA Manager​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

Oracle Role Projects Team Lead​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD]

FA Manager​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD]

GL User​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

Oracle Role Projects Team Member​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD][/TD]
[TD]

AP Inquiry​

[/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]

 

[alansidman]

Load the data to Power Query and Unpivot all but the first column. Close and Load to your spreadsheet and then create a pivot table putting all data into rows and eliminating subtotals and grand totals.

To further understand and learn the value of Power Query you can obtain the book M is for (Data) Monkey by Ken Puls and Miguel Escobar. This is a very powerful tool that allows for easy manipulation of data.