Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default compare names and delete duplicates

I am trying to change a macro. Originally I had a worksheet (DL info) with
Lastname, Firstname format in column A and the macro below worked to compare
to worksheet (Krofta) and delete duplicates. The worksheet Kroft had
LastName in column A and FirstName in column B.

Since then I've grown and am now using a macro to split the names on Kroft
worksheet into Lastname in column A and Firstname in column B - just like my
DL info worksheet. I'm not getting the macro changed properly so it will
now compare LastName to LastName and FirstName to FirstName. My goal is to
compare the 2 worksheets and delete any duplicates from the Krofta
worksheet.

Macro will follow. Any help is much appreciated. Thanks!

Sub DeleteMatch()
'This compares LastName and FirstName on DL info to Username on Krofta
'testing to try to get to compare LastName to LastName and FirstName to
Firstname
Dim x, Rng1 As Range, LstRow As Integer
Dim LastName, FirstName, FirstAdd

With Worksheets("Krofta")
Set Rng1 = .Range(.Cells(1, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With

With Worksheets("DL info")
LstRow = Cells(Rows.Count, "A").End(xlUp).Row
End With

For I = LstRow To 1 Step -1
LastName = Worksheets("DL info").Cells(I, "D")
FirstName = Worksheets("DL info").Cells(I, "B")
With Rng1
Set x = .Find(LastName, , xlValues)
If Not x Is Nothing Then 'Match found
FirstAdd = x.Address
Do
If Right(x, Len(x) - InStr(x, ",") - 1) = FirstName Then
'Both First and Last Names Match
Worksheets("DL").Rows(I).Delete Shift:=xlUp
Exit Do
Else
Set x = .FindNext(after:=Cells(x.Row, 1))
End If
Loop Until x Is Nothing Or x.Address = FirstAdd
End If
End With
Next

End Sub



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare columns for duplicates acab New Users to Excel 2 October 3rd 08 08:14 AM
compare and merge duplicates Net New Users to Excel 2 February 8th 07 01:53 PM
Compare Duplicates [email protected] Excel Discussion (Misc queries) 3 October 13th 06 03:16 AM
How to compare two columns and remove duplicates? username123 Excel Discussion (Misc queries) 15 July 5th 06 04:06 PM
how do i compare two columns and remove duplicates? aljernon805 New Users to Excel 1 December 9th 05 04:04 PM


All times are GMT +1. The time now is 09:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"