Complex Filter

[Karen53]

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.

 

[Jim Cone]

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" <[email protected]>
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.

 

[Karen53]

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?

 

[Jim Cone]

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" <[email protected]>
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?

 

[Jim Cone]

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

 

[Karen53]

Thank you again Jim! This is great!