Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Duplicate Entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Duplicate Entries
Please clarify your example. You say that John Smith & Sean Mann share 2
classes, but that Julie Jones doesn't, and yet Julie Jones is in 84744.4 (as are John & Sean). Why is Julie Jones excluded from the "shares 84744.4 with John & Sean" list? "Elliot" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Duplicate Entries
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Duplicate Entries
Thank you very much for that JLatham
The Macro works perfectly!!!!!! "JLatham" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicate Cell Entries | Excel Discussion (Misc queries) | |||
Finding duplicate time punch entries | Excel Discussion (Misc queries) | |||
Finding duplicate entries | Excel Worksheet Functions | |||
Finding Duplicate Entries | Excel Discussion (Misc queries) | |||
Need help with finding duplicate entries | Excel Worksheet Functions |