How to move cells to form a table

[Jonathan Lee]

Dear all, need your help here.

This is my current situation.

Company Name	User 1	User 2	User 3	User 4
ABC Ltd	Read	Read	No Access	Read / Write
BCD Ltd	Read / Write	No Access	No Access	No Access
DEF Ltd	No Access	Read / Write	Read / Write	No Access

You see, with the above, I cannot do any work unless I can put them into the following format:

User Name	Company Name	Access Rights
1	ABC Ltd	Read
1	BCD Ltd	Read / Write
1	DEF Ltd	No Access
And so on....	And so on...	And so on...

Question is, how can I get to the above? If a Macro is required, kindly show me the steps.

Thanks a million.

 

[alansidman]

If this presentation works for you, then you can get it by unpivoting your data in Power query and then bringing the data into Power Pivot and creating a pivot table and creating a measure to change the values to text using the concatenatex function and placing in the values section of your pivot table.

Book10
	G	H	I
1	Attribute	Company	Access
2	User 1	ABC Ltd	Read
3		BCD Ltd	Read / Write
4		DEF Ltd	No Access
5	User 2	ABC Ltd	Read
6		BCD Ltd	No Access
7		DEF Ltd	Read / Write
8	User 3	ABC Ltd	No Access
9		BCD Ltd	No Access
10		DEF Ltd	Read / Write
11	User 4	ABC Ltd	Read / Write
12		BCD Ltd	No Access
13		DEF Ltd	No Access
Sheet1

 

[Jonathan Lee]

If this presentation works for you, then you can get it by unpivoting your data in Power query and then bringing the data into Power Pivot and creating a pivot table and creating a measure to change the values to text using the concatenatex function and placing in the values section of your pivot table.

Book10
	G	H	I
1	Attribute	Company	Access
2	User 1	ABC Ltd	Read
3		BCD Ltd	Read / Write
4		DEF Ltd	No Access
5	User 2	ABC Ltd	Read
6		BCD Ltd	No Access
7		DEF Ltd	Read / Write
8	User 3	ABC Ltd	No Access
9		BCD Ltd	No Access
10		DEF Ltd	Read / Write
11	User 4	ABC Ltd	Read / Write
12		BCD Ltd	No Access
13		DEF Ltd	No Access
Sheet1

Thanks!!! It works great!