Help with a matching code

[jayploc]

Hello,

I have a workbook with 3 sheets. The first 2 sheets titled 'FALL 07 SI' and 'FALL 08 SI' respectively are total rosters of students who attended tutoring in those semesters (both are in the same format). The third sheet titled 'SI in 07 AND 08' is set up with the same column titles as the first 2 sheets.

What I want to do is make a roster of the people who attended in BOTH semesters (appear in sheet 1 AND 2) with no duplicates... because some names do appear multiple times in one sheet. I want the information in each column to also be copied to the third sheet so it is the same format as the first two.

Thanks to anyone who can help!

[mdmackillop]

Extracting numbers is not a problem, but I'm not clear how the column data is used.


Option Explicit
Sub Names()
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim cel As Range, c As Range, d As Range
With Sheets(1)
Set Rng1 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With Sheets(2)
Set Rng2 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Set Rng3 = Sheets(3).Columns(1)
For Each cel In Rng1
Set c = Rng2.Find(cel, lookat:=xlWhole)
Set d = Rng3.Find(cel, lookat:=xlWhole)
If Not c Is Nothing And d Is Nothing Then
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1) = cel
End If
Next
End Sub

[jayploc]

This code returned the "STUDENT_NUMBER" column, which is a start. I need the whole row of information for each Student Number... it seems like it might just require a small modification to the code you gave me but I have no idea how to do that.

I'm not really sure what your question is about using the column data. The information that was matched correctly (I assume...), I just want it to output the entire row (all columns) of information available for each of the students.

If you have any more questions about it, let me know so I can explain it differently.

Thanks,
Jay

[BrianMH]

You say you don't want duplicates? So you don't want one name to appear more than once? If so which data from the rest of the columns do you want to keep as these are different for each row?

[mdmackillop]

You say you don't want duplicates? So you don't want one name to appear more than once? If so which data from the rest of the columns do you want to keep as these are different for each row?
Exactly!!

[jayploc]

Oh good point. Sorry I didn't even think of that. I don't really care which of the duplicates get deleted. The most important columns of information I want are tha last and first name columns. If those two columns, in addition to the STUDENT_NUMBER column appear in the third sheet, that would be perfect.

If it is easier to leave the duplicates, that is fine. Hope that clears that up.

[BrianMH]

Option Explicit
Sub Names1()
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim cel As Range, c As Range, d As Range
With Sheets(1)
Set Rng1 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With Sheets(2)
Set Rng2 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Set Rng3 = Sheets(3).Columns(1)
For Each cel In Rng1
Set c = Rng2.Find(cel, lookat:=xlWhole)
Set d = Rng3.Find(cel, lookat:=xlWhole)
If Not c Is Nothing And d Is Nothing Then
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1) = cel.Value
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = cel.Offset(0, 1).Value
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = cel.Offset(0, 2).Value
End If
Next
End Sub





Theres the modification you needed to get the last and first names.

[mdmackillop]

Thanks Brian,
You can trim this to save a couple of lines though
If Not c Is Nothing And d Is Nothing Then
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = cel.Resize(, 3).Value
End If

[jayploc]

Thanks guys.

I used both of your codes and modified the if statement to this:


If Not c Is Nothing And d Is Nothing Then
Sheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 16) = cel.Resize(, 16).Value
End If



I'm pretty sure it will work this way.

Thanks