sorting in multiple data patterns

[theguruguy]

my problem is that i have a list of user id's in column A. in columns B-? are permissions associated with that user ID.
I need to go through every row and sort by permission that would fit in a similar category. For example if a column value starts with grp.app.xxx
I would want to move all those to the same column on a different sheet. that new header column could be called apps.
then it continues is evaluation of the rows, doing the same thing for grp.ctx.xxx, moving to a new column on the new sheet with the header citrix, and again from grp.share.xxx. ect,ect. so columns could have more than one value in them so the script would have to append the data. most importantly keeping the data associated with the user.

after it finishes the row it would need to go to next row and do it again. about 1000 user id's

after is is all over it would be great to sort the columns with multiple values alphabetically..

im so lost on this.

 

[alansidman]

You need to rename the table/range to be uploaded to Power Query as "Table1" without the quotes.

 

[Akuini]

Please excuse me for being a beginner.. when i run this nothing happens... i dont see where the compare items are.. like looking for 'grp.app'
thanks.

I don't understand, using your example above, here's the result in sheet2:

Book1
	A	B	C	D	E
1	user Id	.ctx.	.share.	.vdi.	.app.
2	user1	grp.ctx.ctx1	grp.share.share100		grp.app.app7, grp.app.app15
3	user2	grp.ctx.ctx10		grp.vdi.vdi20	grp.app.app10, grp.app.app2
Sheet1

<p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

 

[theguruguy]

thank you, I got it to work.. but still unsure of how to get the view i need with how the data is now formatted.

like I posted previously.. there are a bunch of different random user access that i need to categorize.

i would like to put things like. grp.app.xx1, grp.app.xx2 in the same cell. not sure how to do this.

 

[theguruguy]

I don't understand, using your example above, here's the result in sheet2:

	A	B	C	D	E
1	user Id	.ctx.	.share.	.vdi.	.app.
2	user1	grp.ctx.ctx1	grp.share.share100		grp.app.app7, grp.app.app15
3	user2	grp.ctx.ctx10		grp.vdi.vdi20	grp.app.app10, grp.app.app2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

when i run the code i get the following

runtime error 5
Invalid procedure call or argument.

z = Mid(x, m, n - m + 1)

 

[Akuini]

when i run the code i get the following

runtime error 5
Invalid procedure call or argument.

z = Mid(x, m, n - m + 1)

Do you mean you got an error when using the data sample in post #1 or when using your real data?