Deleting entire rows of duplicate names

[casexcel]

I have been given and excel spreadsheet that has combined 2 data bases.

One data base was a list of all students
One data base was a list of all "on" campus students
I want my final list to equal just students who live "Off" campus

The columns are set up as follows:

A = First Name
B = Last Name
C, D, E, F = Address, City, State, Zip....etc (not important)

So.... after doing a sort by A, then B, the only duplicates will be the students that live on campus. Therefore, deleting ALL duplicate entries will leave me with a list of all Off campus students.

Because we have a lot of duplicate last names, but different first names.

Example:

Column A Column B
Row LastName FirstName
1 Adams Becky
2 Adams Becky
3 Adams Joe
4 Adams Linda
5 Adams Melissa
6 Adams Melissa

Is there a macro to find duplicate Last and First name, then delete them both completely?

ie: I need rows 1, 2, 5, 6 deleted - Leaving Joe and Linda Adams

Thank you

Catherine

 

[Kenneth Hobson]

Steps:
1. Make a column to add the two cells in say column C if you had just the two columns of first and last names. Copy the formula down.
e.g. B2=A2&" "&B2
2. Add another column (D) to count and copy down.
e.g. D2=COUNTIF($D$2:$D$7,D2)
3. Add Auto Filter by Data > Filter > AutoFilter.
4. Set the filter for Column D to 1.
5. Copy that data to another Sheet or location.

 

[facethegod]

Welcome to the Board! This macro may do what you need. Please make sure you have another copy of your original workbook before running the macro.

1.Copy below code.
2.In Excel press Alt + F11 to enter the VBE.
3.Press Ctrl + R to show the Project Explorer.
4.Right-click desired file on left (in bold).
5.Choose Insert -> Module.
6. Make sure the sheet you want to run the code is the activesheet.
6.Paste code into the right pane.
7.Press F5 to run
8.Press Alt + Q to close the VBE.

Option Explicit
Sub tst()
Dim r As Range, b() As Variant, a, i As Integer, bTest As Boolean
Set r = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row): a = r
ReDim b(1 To UBound(a, 1))
For i = 1 To UBound(a, 1): b(i) = a(i, 1) & ";" & a(i, 2): Next
With r.Resize(UBound(b), 1): .Value = Application.Transpose(b)
For i = 1 To UBound(b, 1)
bTest = (Application.CountIf(.Cells, b(i)) > 1)
b(i) = IIf(bTest, CVErr(xlErrNA), a(i, 1))
Next
.Value = Application.Transpose(b)
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
End Sub

HTH

 

[casexcel]

I tried to do both these options.

The first suggestion - deleted everything from my spreadsheet.

The second gave me a Run-time error '1004'

Any other suggestions?

I'll go through and delete them manually if needed.

 

[Kenneth Hobson]

AutoFilter does not delete data. Here is an example: http://www.mediafire.com/?f8nzjvxv2rw

 

[casexcel]

AH.... I've got it. It worked. (As if you had any doubt)

You guys are the best. Saved me a ton of work!!!

Thanks for your time.

Catherine

 

[Lidsavr]

Like the originator of the message, I too am trying to delete rows where duplicate data is found in the first row. I searched to forum and found the quoted material below and tried it, but received an error that says, "Run-time error '1004': No cells were found.

Here is an example of my data (I have bolded the data that I want to delete because there is duplicate data in the first column)

ColumnA*****ColumnB*****ColumnC
Smith*******January******5
Smith*******January******6
Jones*******February*****3
Stolhanske***March*******12
Stolhanske***April*********3
Smith*******April*********4
Jones*******May********10
Jones*******May********3
Jones*******May********4
Sunga******June********8

So when I am done running the macro, the data looks like this:

ColumnA*****ColumnB*****ColumnC
Smith*******January******5
Jones*******February*****3
Stolhanske***March*******12
Smith*******April*********4
Jones*******May********10
Sunga******June********8

Please look at the code below and confirm if I am correct (that this code will work) and I am doing something wrong with it. If I am doing something wrong, please let me know what it is. If it will not work for my application, will you suggest something that will?

Thank you very much,

Charles (Lidsavr) ray:

Welcome to the Board! This macro may do what you need. Please make sure you have another copy of your original workbook before running the macro.

1.Copy below code.
2.In Excel press Alt + F11 to enter the VBE.
3.Press Ctrl + R to show the Project Explorer.
4.Right-click desired file on left (in bold).
5.Choose Insert -> Module.
6. Make sure the sheet you want to run the code is the activesheet.
6.Paste code into the right pane.
7.Press F5 to run
8.Press Alt + Q to close the VBE.

Option Explicit
Sub tst()
Dim r As Range, b() As Variant, a, i As Integer, bTest As Boolean
Set r = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row): a = r
ReDim b(1 To UBound(a, 1))
For i = 1 To UBound(a, 1): b(i) = a(i, 1) & ";" & a(i, 2): Next
With r.Resize(UBound(b), 1): .Value = Application.Transpose(b)
For i = 1 To UBound(b, 1)
bTest = (Application.CountIf(.Cells, b(i)) > 1)
b(i) = IIf(bTest, CVErr(xlErrNA), a(i, 1))
Next
.Value = Application.Transpose(b)
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
End Sub

HTH

 

[Lidsavr]

Sorry, I tried to post this as a new message from me, but it didn't work out. Please ignore this message and see the new message from from me on this forum.

Charles (Lidsavr)