Transpose data dynamically

[Analytics_Pooja]

I current have data in this format. 

Collection Name	Owner ID	Date	Added by	User 1	Can Add	Can Remove	User 2	Can Add2	Can Remove2
test1	hf4387230hf	Sept 04, 2021	Admin 1	Tom	False	False	Harry	True	True
test2	ewdj8934e32	Sept 07, 2021	Admin 2	Jerry	True	True	Perry	True	True

After transpose the desired output is:

Collection Name	Owner ID	Date	Added by	User	Can Add	Can Remove
test1	hf4387230hf	Sept 04, 2021	Admin 1	Tom	False	False
test1	hf4387230hf	Sept 04, 2021	Admin 1	Harry	True	True
test2	ewdj8934e32	Sept 07, 2021	Admin 2	Jerry	True	True
test2	ewdj8934e32	Sept 07, 2021	Admin 2	Perry	True	True

The purpose is to transpose  User, Can Add and Can Remove column to get single column for each. 

User, "Can add" and "Can Remove"  - number of columns will increase dynamically. Example, there could be user 4 or user 5 as well for certain collections. 

Any help would be much appreciated. 

Best

Pooja 

[mst3k · Accepted solution]

you can do it with a transpose and a crosstab. the trick is the tile tool to give a key to "pivot" it around. the regex tool strips the "2", "3", "4" etc from your fields names, that could cause problems if any of the fields should keep a number in it...

this works fine if you add more users, as you can see here i added a "Zach" to admin 1 and it runs fine.

[BrandonB · Accepted solution]

Nice solution @mst3k!

I took a slightly different approach with the same result

[Analytics_Pooja]

This works perfectly! Thank you