Log In
Register
Home
December 4, 2021
Offline
I have been trying to figure this one out, and I am at a loss. There are over 14K rows of data, so doing this manually is NOT an option. I have attached a spreadsheet with 2 tabs, named "before" and "after".
- Before indicates how I receive the data. Users are listed in Column B. These users are listed multiple times for each role they have. The roles are listed in Column D. A user could have as many as 7 roles maximum.
- After shows how I need this data to display. There should only be one record for each user, and there would be Role columns 1-7 Columns D-H.
I know there must be a way to do this in VBA, but I just cannot figure it out. Any help is greatly appreciated.
See Answer
Trusted Members
October 17, 2018
Offline
Hi, which version of Excel are you using?
How's your VBA knwoledge?
Trusted Members
October 17, 2018
Offline
This is done without VBA but will only work with 365 or 2021
Answers Post
Trusted Members
Moderators
January 31, 2022
Offline
Alternatively, use Power Query, as shown in the attached file.
December 4, 2021
Offline
@Hans Hallebeek,
Thanks for the well detailed answer. I was able to easily apply the logic you defined to my live file. I added an IF Statement so it did not display the zeros.
@Riny van Eekelen,
I have downloaded your PQ workbook so I can also learn that way to get to the answer. I love PQ!!!!
1 Guest(s)
