Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
Hi,
I don't know as though this is possible but I thought I would give it a go. Column A Cols B:F Column G Teacher Student Column A will have the same Teacher listed for multiple students in Column G. Is it possible to filter to all of the Teachers that have a specific student but showing all students in the class as well? For example filtering to Henry: Col A Cols B:F Col G Nancy Jones Whatever George Nancy Jones Henry Nancy Jones Mary Nancy Jones Susan John Henry Albert John Henry Lewis John Henry Mary John Henry Henry etc. The way I think of doing it would be time consuming and would involve uploading the data to another sheet. For example, search for the matching student. Once a match is found, load the teacher into an array. Then copy all rows for all of those teachers onto a sheet. Is there a way of just filtering the existing sheet? I need to be able to edit data in this filter so another sheet won't do it. -- Thanks for your help. Karen53 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
Try this code on a copy of your sheet (just in case).
It adds a X to the "H" column for each teacher with Henry in their class. (assumes data starts in row 4) You can then filter on the H column... '-- Sub LineThemUp() 'Jim Cone - Portland, Oregon USA - August 2009 Dim rngS As Range Dim rngT As Range Dim sCell As Range Dim tCell As Range Dim NameToFind As String Dim strNames As String Dim arrNames As Variant Dim vItem As Variant NameToFind = "Henry" Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp)) Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp)) 'Find the student name and save the teacher name. For Each sCell In rngS If sCell.Value = NameToFind Then strNames = strNames & sCell(1, -5).Value & "," End If Next arrNames = Split(strNames, ",", -1, vbBinaryCompare) 'Find the saved teachers name and add X to that row. For Each tCell In rngT For Each vItem In arrNames If vItem Like tCell.Value Then tCell(1, 8).Value = "X" Exit For End If Next Next End Sub -- Jim Cone Portland, Oregon USA "Karen53" wrote in message ... Hi, I don't know as though this is possible but I thought I would give it a go. Column A Cols B:F Column G Teacher Student Column A will have the same Teacher listed for multiple students in Column G. Is it possible to filter to all of the Teachers that have a specific student but showing all students in the class as well? For example filtering to Henry: Col A Cols B:F Col G Nancy Jones Whatever George Nancy Jones Henry Nancy Jones Mary Nancy Jones Susan John Henry Albert John Henry Lewis John Henry Mary John Henry Henry etc. The way I think of doing it would be time consuming and would involve uploading the data to another sheet. For example, search for the matching student. Once a match is found, load the teacher into an array. Then copy all rows for all of those teachers onto a sheet. Is there a way of just filtering the existing sheet? I need to be able to edit data in this filter so another sheet won't do it. -- Thanks for your help. Karen53 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
Thank you Jim! This is perfect!
I have a question, if you don't mind. I would like to understand this so I may modify it if any columns are added or deleted. Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp)) Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp)) 'Find the student name and save the teacher name. For Each sCell In rngS If sCell.Value = NameToFind Then strNames = strNames & sCell(1, -5).Value & "," End If Next Here is my confusion. sCell(1, -5). If the students are in column 7 and the teachers are in column 1, why is it -5? Why isn't it -6? I am assuming a negative column count from the column containing rngS. Also, what does the 1 mean, row? Current row, maybe? Ground zero for the student column? -- Thanks for your help. Karen53 "Jim Cone" wrote: Try this code on a copy of your sheet (just in case). It adds a X to the "H" column for each teacher with Henry in their class. (assumes data starts in row 4) You can then filter on the H column... '-- Sub LineThemUp() 'Jim Cone - Portland, Oregon USA - August 2009 Dim rngS As Range Dim rngT As Range Dim sCell As Range Dim tCell As Range Dim NameToFind As String Dim strNames As String Dim arrNames As Variant Dim vItem As Variant NameToFind = "Henry" Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp)) Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp)) 'Find the student name and save the teacher name. For Each sCell In rngS If sCell.Value = NameToFind Then strNames = strNames & sCell(1, -5).Value & "," End If Next arrNames = Split(strNames, ",", -1, vbBinaryCompare) 'Find the saved teachers name and add X to that row. For Each tCell In rngT For Each vItem In arrNames If vItem Like tCell.Value Then tCell(1, 8).Value = "X" Exit For End If Next Next End Sub -- Jim Cone Portland, Oregon USA "Karen53" wrote in message ... Hi, I don't know as though this is possible but I thought I would give it a go. Column A Cols B:F Column G Teacher Student Column A will have the same Teacher listed for multiple students in Column G. Is it possible to filter to all of the Teachers that have a specific student but showing all students in the class as well? For example filtering to Henry: Col A Cols B:F Col G Nancy Jones Whatever George Nancy Jones Henry Nancy Jones Mary Nancy Jones Susan John Henry Albert John Henry Lewis John Henry Mary John Henry Henry etc. The way I think of doing it would be time consuming and would involve uploading the data to another sheet. For example, search for the matching student. Once a match is found, load the teacher into an array. Then copy all rows for all of those teachers onto a sheet. Is there a way of just filtering the existing sheet? I need to be able to edit data in this filter so another sheet won't do it. -- Thanks for your help. Karen53 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
You are welcome.
sCell(1, 1) is the cell at the first row and first column respectively of sCell. Since sCell is one cell then sCell(1, 1) is sCell. The above is shorthand for sCell.Cells(1, 1) sCell(1, 0) is one column to the left and sCell(1, -1) is two columns to the left. (all on the same row) These callouts all apply to the range they are attached to. Separately, you should note that the "Offset" method has a base of (0, 0). So that... sCell.Offset(1, 1) would be the cell one row down and one row to the right. '-- You can verify all of this in a blank workbook by using a message box to display the address of specific cells... '-- Sub WhereAreYou() MsgBox ActiveCell.Address & _ vbCr & ActiveCell(1, 1).Address & _ vbCr & ActiveCell.Offset(1, 1).Address End Sub '-- Try changing the (1, 1) designations and see what you get. Jim Cone Portland, Oregon USA "Karen53" wrote in message ... Thank you Jim! This is perfect! I have a question, if you don't mind. I would like to understand this so I may modify it if any columns are added or deleted. Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp)) Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp)) 'Find the student name and save the teacher name. For Each sCell In rngS If sCell.Value = NameToFind Then strNames = strNames & sCell(1, -5).Value & "," End If Next Here is my confusion. sCell(1, -5). If the students are in column 7 and the teachers are in column 1, why is it -5? Why isn't it -6? I am assuming a negative column count from the column containing rngS. Also, what does the 1 mean, row? Current row, maybe? Ground zero for the student column? -- Thanks for your help. Karen53 "Jim Cone" wrote: Try this code on a copy of your sheet (just in case). It adds a X to the "H" column for each teacher with Henry in their class. (assumes data starts in row 4) You can then filter on the H column... '-- Sub LineThemUp() 'Jim Cone - Portland, Oregon USA - August 2009 Dim rngS As Range Dim rngT As Range Dim sCell As Range Dim tCell As Range Dim NameToFind As String Dim strNames As String Dim arrNames As Variant Dim vItem As Variant NameToFind = "Henry" Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp)) Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp)) 'Find the student name and save the teacher name. For Each sCell In rngS If sCell.Value = NameToFind Then strNames = strNames & sCell(1, -5).Value & "," End If Next arrNames = Split(strNames, ",", -1, vbBinaryCompare) 'Find the saved teachers name and add X to that row. For Each tCell In rngT For Each vItem In arrNames If vItem Like tCell.Value Then tCell(1, 8).Value = "X" Exit For End If Next Next End Sub -- Jim Cone Portland, Oregon USA "Karen53" wrote in message ... Hi, I don't know as though this is possible but I thought I would give it a go. Column A Cols B:F Column G Teacher Student Column A will have the same Teacher listed for multiple students in Column G. Is it possible to filter to all of the Teachers that have a specific student but showing all students in the class as well? For example filtering to Henry: Col A Cols B:F Col G Nancy Jones Whatever George Nancy Jones Henry Nancy Jones Mary Nancy Jones Susan John Henry Albert John Henry Lewis John Henry Mary John Henry Henry etc. The way I think of doing it would be time consuming and would involve uploading the data to another sheet. For example, search for the matching student. Once a match is found, load the teacher into an array. Then copy all rows for all of those teachers onto a sheet. Is there a way of just filtering the existing sheet? I need to be able to edit data in this filter so another sheet won't do it. -- Thanks for your help. Karen53 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
Correction...
sCell.Offset(1, 1) would be the cell one row down and one row to the right. Should be... sCell.Offset(1, 1) would be the cell one row down and one column to the right. '-- Jim Cone |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Filter
Thank you again Jim! This is great!
-- Thanks for your help. Karen53 "Jim Cone" wrote: Correction... sCell.Offset(1, 1) would be the cell one row down and one row to the right. Should be... sCell.Offset(1, 1) would be the cell one row down and one column to the right. '-- Jim Cone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
Complex look up/colum filter | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |