Dynamic filtering into a new range

[Atreides]

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

 

[Jacob Skaria]

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

 

[Atreides]

Hi Jacob,

Thanks for taking the time to answer my question and write up that code.
However what I was looking for was a more generic solution that I can use in
the future.

I kept on searching and here's what I found to do the job.

{=STRINGCONCAT(", ",1,IF(Task1Yes="y",Names,""))}

For the benefit of others like me:
- STRINGCONCAT is a great little user-defined function I found by a guy
called Chip Pearson. It actually does what you'd think Excel's CONCATENATE
should do. You can give it a range, array or whatever and specify a separator
(in this case, I've used ", ") and it will concatenate away. There is also an
option for skipping blanks and it knows not to put a separator at the end of
the list. I was so stoked when I found it that I had to share.
- Here, "Task1Yes" and "Names" are defined names for ColB and ColA (as per
Jacob's reply)
- The IF function is applied over the array. So anything that satisfies the
test (="y") will be included in the array and anything that fails will be
left blank.

Again, thanks for your help and time but the thing I like about my solution
is that it's generic. Now that I know about this function, I can use it again
in other situations. I have no doubt that your code would work in this case
but I lack the skills to modify it to use in other situations.

Thanks again
Atreides.

 

[Jacob Skaria]

--Thanks for sharing that.

--The macro posted is a generic one. As long as your data range starts from
cell A1 you dont need to modify.

If this post helps click Yes