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.
Hi, which version of Excel are you using?
How's your VBA knwoledge?
This is done without VBA but will only work with 365 or 2021
Alternatively, use Power Query, as shown in the attached file.
@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!!!!
