Excel Vlookup or formula

Question

Can someone suggest a formula in Excel for the below;

I have the following data in a sheet1 where users have multiple roles assigned to them, users have multiple roles and each is listed in a separate row

A----------B
User 1 - Role 1
User 1 - Role 2
User 1 - Role 5
User 2 - Role 2
User 2 - Role 5
User 3 - Role 1
User 3 - Role 3

In another sheet2 I have all unique user names where I want the data from Sheet 1 to be listed

A----------B-------C---------D
User 1 - Role 1 - Role 2 - Role 5
User 2 - Role 2 - Role 5
User 3 - Role 1 - Role 3

Answer 1

Use below formula in sheet2-

=IFERROR(INDEX(Sheet1!$B$1:$B$7,AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$7)/(Sheet1!$A$1:$A$7=$A1),COLUMN(A$1))),"")

If you have Office365 then you can simplify it by Transpose() and Filter() formula-

=TRANSPOSE(FILTER(Sheet1!$B$1:$B$7,Sheet1!$A$1:$A$7=A1))

Answer 2

I'm not sure this is possible with formulas, but VBA would do it as follows

Dim Lrow as Long
Lrow = shtFrom.Range("A1").End(xlUp).Row
Dim RoleCount as Long
Dim shtFrom as Worksheet, shtTo as Worksheet

Set shtFrom = Sheets("Sheet1")
Set shtTo = Sheets("Sheet2")

For x = 1 to shtTo.Range("A1").End(xlUp).Row
    RoleCount = 0
    For y = 1 to Lrow
        If shtFrom.Cells(y, 1).Value Like shtTo.Cells(x, 1).Value Then
            RoleCount = RoleCount + 1
            shtTo.Cells(y, RoleCount + 1).Value = shtTo.Cells(x, 2).Value
        End If
    Next y
Next x