Excel Compare Function

[ckugo]

I need some assistance with searching through an Excel spreadsheet to compare data. I have one column that I need to search through and compare values. When a duplicate value exists I need to delete the entire row. I am somewhat familiar with VBA, but I need some assistance with this. All the help I get is greatly appreaciate. Does anyone have any ideas??

Thanks,

Chris

 

[RonRepp]

Chris, try this:

Sub DeleteDupes()
'Change to 1st cell of the column you are looking for dupes in.
Range("A1").Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Do
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).EntireRow.Delete xlUp
Else
ActiveCell.Offset(1, 0).Activate
End If
If ActiveCell.Value = "" Then Exit Sub

Loop

End Sub

 

[ckugo]

Thank you so much for the sub routine Ron. However, the sub makes my first value turn into a 0 and does not delete any of the duplicates. I am not famaliar enough with the code to try and troubleshoot what it is doing.

I can actually do an advanced filter and select the option not to contain duplicates for that certain column, but how can I actually delete the hidden rows instead of just "hiding" them. When I save the file and re-open it the duplicates re-appear. Eveyone's help is greatly appreciated.

Thanks,

Chris

 

[TonyJollans]

Hi Chris,

You shouldn't need code:

Assuming your duplicates are in column A.

Find an empty column and enter this formula in row 2 of that column (assuming you have headings in row 1):
[blue][tt] =VLOOKUP($A2,$A$1:$A1,1,FALSE)[/tt][/blue]

Copy it down as far as your data.
Autofilter on this new column > Custom > Does Not Equal #N/A
Delete all visible rows
Delete the helper column
Switch off Autofilter na dyou're done.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ckugo]

Thanks so much for the input Tony. This is a very good function and almost does the trick. However, if the data in the column is blank or is the last row of data it will not return the correct value.

Actually, after looking at it again, the error only occurs when the row beneath the one that is being checked is blank. Vlookup then returns a "0." I have tried to put 2 criteria on the auto filter and it returns an error.

Any more thoughts??

Thanks so much,

Chris

 

[TonyJollans]

Hi Chris,

The problem is that zeroes and blank cells are effectively treated the same. I think this adjustment will tweak it so that they are treated differently whilst maintaining the rest as was:

[blue][tt] =IF(ISBLANK($A2),IF(COUNTBLANK($A$1:$A1)>0,"",NA()),VLOOKUP($A2,$A$1:$A1,1,FALSE))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[MJPPaba]

in this example you are using the advanced filter. the data is set to the range A1 to H5 and a copy of the data is created starting from cell J1. The Unique option allows for no duplicates of data to be admitted to the new records.

Sub myAdvancedFilter()
Range("A1:H5").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1"), Unique:=True
End Sub

cheers

Matthew

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[ckugo]

Thanks alot to everyone who helped out with this equation. Tony, your solution seemed to work best for my situation and I greatly appreaciate it. Thanks again to everyone.

Chris