If you can use a VBA/Macro solution, I believe this will do the trick for
you. You provide a name, and it will list all students sharing classes with
that student. The output is grouped by class number.
To put the code into your workbook: open the workbook, press [Alt]+[F11] and
then choose Insert --> Module. Copy the code below and paste it into the
module and make any changes to the Const values that you feel required (most
likely the least you'll have to change the 2 worksheet names, which can be
the same if you want it all done on a single sheet). Then give it a try from
Tools --> Macro --> Macros, or put a control or shape on a sheet and attach
macro to it for eas of access.
The code:
Sub FindStudentsSharingClasses()
'given a student name, identify
'all students sharing that class
'written to do it all on a single sheet,
'but also written so that it could be
'used with 2 sheets (1 for source data
'and a second one with results output)
'
'definitions for the 'source list' sheet
Const srcSheetName = "Sheet1"
Const nameCol = "A"
Const classNumCol = "B"
'definitions for 'output' sheet
'this uses same sheet, but you can
'change destSheetName to put results on
'a different sheet
Const destSheetName = "Sheet1"
'cell to enter name to find
'matches for (on the output sheet)
Const seekNameInCell = "E1"
'column to put results into
Const outputCol = "F"
'end of user definable constants
Dim srcWS As Worksheet
Dim namesRange As Range
Dim anyName As Range
Dim classNumRange As Range
Dim anyClassNum As Range
Dim destWS As Worksheet
Dim seekName As String
Dim classNumber As Variant
Dim nextRow As Long
Set destWS = Worksheets(destSheetName)
If IsEmpty(destWS.Range(seekNameInCell)) Then
'no name entered to match up to
Set destWS = Nothing
Exit Sub
End If
Set srcWS = Worksheets(srcSheetName)
'save name to match on in all UPPER CASE and
'with leading/trailing blanks removed
seekName = UCase(Trim(destWS.Range(seekNameInCell)))
Set namesRange = srcWS.Range(nameCol & "1:" & _
srcWS.Range(nameCol & Rows.Count).End(xlUp).Address)
Set classNumRange = srcWS.Range(classNumCol & "1:" & _
srcWS.Range(nameCol & Rows.Count).End(xlUp) _
.Offset(0, 1).Address)
'clear any previous results in the output column
destWS.Columns(outputCol & ":" & outputCol).Clear
destWS.Columns(outputCol & ":" & outputCol).Offset(0, 1).Clear
For Each anyName In namesRange
If UCase(Trim(anyName)) = seekName Then
classNumber = srcWS.Range(classNumCol & anyName.Row)
'output the initial name and class number
nextRow = _
destWS.Range(outputCol & _
Rows.Count).End(xlUp).Offset(2, 0).Row
destWS.Range(outputCol & nextRow) = seekName
destWS.Range(outputCol & nextRow).Offset(0, 1) = classNumber
'begin seeking matches to the class
For Each anyClassNum In classNumRange
If anyClassNum = classNumber And _
UCase(Trim(srcWS.Range(nameCol & anyClassNum.Row))) _
<> seekName Then
nextRow = _
destWS.Range(outputCol & _
Rows.Count).End(xlUp).Offset(1, 0).Row
destWS.Range(outputCol & nextRow) = _
srcWS.Range(nameCol & anyClassNum.Row)
destWS.Range(outputCol & nextRow).Offset(0, 1) = _
classNumber
End If
Next
End If
Next
'cleanup and housekeeping
Set classNumRange = Nothing
Set namesRange = Nothing
Set srcWS = Nothing
Set destWS = Nothing
End Sub
Elliot said:
Hi,
I need to find duplicate data from the data table below.
There are 2 columns : one consists of students and the other is an
individual class number. The class number could have 3 or 30 students but it
is UNIQUE.
What I wish to do is to be able to find students in a particular class and
cross reference this to see if there are one or more students that share
subsequent classes.
In the Below example : John Smith and Sean Mann share classes in 84749.2 &
84744.4.
Julie Jones has 2 classes but doesnt share with anyone.
Is there a way to single out all the entries of those that share classes?
Thanks
Elliot
STUDENT CLASS NUMBER
JOHN SMITH 84749.2
SEAN MANN 84749.2
PETER JONES 84749.2
JULIE JONES 84744.7
SARA SMITH 84744.7
BOB SCOTT 84744.7
JOHN SMITH 84744.4
SEAN MANN 84744.4
JULIE JONES 84744.4
