Consolidating and Organizing Data

[MisterJ2024]

Hello Everyone,

I'm not 100% sure how to use pivot tables or all of the features of excel yet.

I have two screenshots. One shows an example spreadsheet with User Names, Groups, Full Names, and Roles.

What I'd like to do is have the data from that sheet, output onto a second sheet.

I'd like the User Name to be listed, and the group to be listed as well, and the role columns to be filled in with the appropriate roles for each group the user is in.

I manually did an example of what I'd like the output to look like across the board.

Can you advise me on how to do this please?

Thanks!

 

[levipe]

Using the formula

=UNIQUE($A$2:$C$28)

will give you a three column list of all the unique user names, groups, and full names. Then, assuming you put the UNIQUE() formula in cell F2, then using

=TRANSPOSE(FILTER($D$2:$D$28,($A$2:$A$28=$F2)*($B$2:$B$28=$G2),"-"))

will return all of the roles associated with the first row. Copy down the TRANSPOSE(FILTER()) function and that should do it.