| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic filtering into a new range
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic filtering into a new range
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic filtering into a new range
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic filtering into a new range
--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 --------------- Jacob Skaria "Atreides" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Filtering...Need Help | Excel Worksheet Functions | |||
| Dynamic Filtering | Excel Discussion (Misc queries) | |||
| dynamic range based on criteria, within a dynamic range, passed to a function | Excel Programming | |||
| creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
| Filtering a dynamic range | Excel Worksheet Functions | |||
Linear Mode
