people assigned to a project - how to know who to go to for each?

[djveed]

Hi everyone! I have a list of programs and for each of them, a list of names that worked on the program, anywhere between one and four people. If I want to synthesize this data and know, for example, what programs should i ask "Geoff" about, how do I do this in Excel?

Program	Person A	Person B	Person C	Person D
Program CNBC	Angela	Geoff
Program CNN	Ashley	Shelby	Geoff
Program ZTV	Ashley	Shelby	Angela	Chris
Program ESPN	Geoff	James
Program Fox	Ashley	Shelby	Chris

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Flyboy65]

Sandy, any chance you could list your steps in post #2

 

[sandy666]

Sandy, any chance you could list your steps in post #2

sure, here is:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Table.UnpivotOtherColumns(Source, {"Program"}, "Attribute", "Value"), {"Value"}, {{"Count", each _, type table}}),
    ProgramList = Table.AddColumn(Group, "Program", each Table.Column([Count],"Program")),
    Sort = Table.Sort(Table.TransformColumns(ProgramList, {"Program", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),{{"Value", Order.Ascending}})
in
    Sort[/SIZE]

 

[mse330]

This is another approach using VBA if interested ... All you need to do is press Alt+F11 then Alt+I+M then paste the below code & hit F5 to run the code

Note : Assuming your data starts in A1 for the header

Sub GetSummary()


Dim Dic As Object, lRow As Long, Ar() As Variant, Ar1 As Variant, k As Variant, Cnt As Long
Set Dic = CreateObject("Scripting.Dictionary")
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Ar = Range("B2:E" & lRow).Value


For Each k In Ar
    If Not Dic.exists(k) And Not IsEmpty(k) Then Dic.Add k, ""
Next


Ar = ActiveSheet.UsedRange.Value
ReDim Ar1(1 To Dic.Count, 1 To 2)


For Each k In Dic.keys
    For x = 1 To UBound(Ar)
        For y = 2 To 5
            If Ar(x, y) = k Then
                Dic(k) = IIf(Dic(k) = "", Ar(x, 1), Dic(k) & ", " & Ar(x, 1))
            End If
        Next y
    Next x
    Cnt = Cnt + 1
    Ar1(Cnt, 1) = k
    Ar1(Cnt, 2) = Replace(Dic(k), "Program ", "") ' If you don't want to remove the word "Program", just place Dic(k) after the = sign
Next k


With Sheets.Add
    .Name = "Summary Report" ' You can change the new sheet name to whatever you wish here
    .Range("A1:B1") = Array("Name", "Program")
    .Range("A2").Resize(UBound(Ar1), 2) = Ar1
    .Columns("A:B").AutoFit
End With


End Sub