Hi "Atreides"

Assuming your data is in the below format with tasknames in Row1 and names
in ColA starting from Row1; The expected output is given below..Try with the
below data and later uou can adjust to suit..try the below macro and
feedback..

Your data:

ColA ColB ColC ColD ColE
Task 1 Task 2 Task 3 Task 4
Alice y y y
Bob y y
Eve y y y

Expected Output:

Task 1 Alice,Bob,Eve
Task 2 Alice
Task 3 Alice,Bob,Eve
Task 4 Eve

Sub Macro()
Dim arrData As Variant, strTemp As String
Dim lngCol As Long, lngLastCol As Long
Dim lngRow As Long, lngLastRow As Long

lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim arrData(1, lngLastCol - 1)

For lngCol = 2 To lngLastCol
strTemp = ""
For lngRow = 2 To lngLastRow
If Cells(lngRow, lngCol) = "y" Then _
strTemp = strTemp & "," & Range("A" & lngRow)
Next
arrData(0, lngCol - 2) = Cells(1, lngCol)
arrData(1, lngCol - 2) = Mid(strTemp, 2)
Next

Range("A" & lngLastRow + 5).Resize(4, 2) = _
WorksheetFunction.Transpose(arrData)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Atreides" wrote:

Hi,

I'm doing up a roster spreadsheet and would like a simple way of adding some
dynamic filtering. Here is an example of what I want to do

Input:
Task 1 Task 2 Task 3 Task 4...
Alice y y y
Bob y y
Eve y y y
...


Output:

Available people
Task 1 Alice, Bob, Eve
Task 2 Alice
Task 3 Alice, Eve
Task 4 Bob
...

OR like this (if easier):

Available people
Task 1 Alice Bob Eve
Task 2 Alice
Task 3 Alice Eve
Task 4 Bob

Using array formulas I can get the following:

Available people
Task 1 Alice Bob Eve
Task 2 Alice
Task 3 Alice Eve
Task 4 Bob ...
...

But this means the table gets very wide. Any ideas?

Thanks
Atreides