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!

Oracle Role	AP User	AP Inquiry	AP Manager	GL Inquiry	GL User	FA Inquiry	FA User	FA Manager
Oracle Role AP Team member	X			X
Oracle Role AP Team Lead			X		X
Oracle Role Projects Team Member		X
Oracle Role Projects Team Lead					X			X
Oracle Role Fixed Asset Team Member		X			X		X
Oracle Role Fixed Asset TeamLead								X

<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>
</tbody>

 

[alansidman]

Would this be an acceptable presentation

Data Range

	A​	B​	C​
1​	Oracle Role​	Attribute​	Value​
2​	Oracle Role AP Team member​	AP User​	X​
3​	Oracle Role AP Team member​	GL Inquiry​	X​
4​	Oracle Role AP Team Lead​	AP Manager​	X​
5​	Oracle Role AP Team Lead​	GL User​	X​
6​	Oracle Role Projects Team Member​	AP Inquiry​	X​
7​	Oracle Role Projects Team Lead​	GL User​	X​
8​	Oracle Role Projects Team Lead​	FA Manager​	X​
9​	Oracle Role Fixed Asset Team Member​	AP Inquiry​	X​
10​	Oracle Role Fixed Asset Team Member​	GL User​	X​
11​	Oracle Role Fixed Asset Team Member​	FA User​	X​
12​	Oracle Role Fixed Asset TeamLead​	FA Manager​	X​

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

	E​	F​
1​	​	​
2​	Oracle Role​	Attribute​
3​	Oracle Role AP Team Lead​	​
4​	​	AP Manager​
5​	​	GL User​
6​	Oracle Role AP Team member​	​
7​	​	AP User​
8​	​	GL Inquiry​
9​	Oracle Role Fixed Asset Team Member​	​
10​	​	AP Inquiry​
11​	​	FA User​
12​	​	GL User​
13​	Oracle Role Fixed Asset TeamLead​	​
14​	​	FA Manager​
15​	Oracle Role Projects Team Lead​	​
16​	​	FA Manager​
17​	​	GL User​
18​	Oracle Role Projects Team Member​	​
19​	​	AP Inquiry​

 

[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

	E​	F​
1​
2​	Oracle Role​	Attribute​
3​	Oracle Role AP Team Lead​
4​		AP Manager​
5​		GL User​
6​	Oracle Role AP Team member​
7​		AP User​
8​		GL Inquiry​
9​	Oracle Role Fixed Asset Team Member​
10​		AP Inquiry​
11​		FA User​
12​		GL User​
13​	Oracle Role Fixed Asset TeamLead​
14​		FA Manager​
15​	Oracle Role Projects Team Lead​
16​		FA Manager​
17​		GL User​
18​	Oracle Role Projects Team Member​
19​		AP Inquiry​

<tbody>
</tbody>

[/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.