Dynamic filtering into a new range

  • Thread starter Thread starter Atreides
  • Start date Start date
A

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
 
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
 
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.
 
--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
 
Back
Top