View Full Version : Check the entire row and delete duplicate values
sindhuja
04-23-2012, 09:47 PM
Hi,
I need to check the values of the entire rows with other rows in the sheet and delete if it duplicates.
Also, i need to filter column D with the criteria *PARK* and copy paste to another sheet and also column E with the criteria *PARK*. The difficulty am facing with this the rows gets duplicated. When i filter i get 10 rows as results and withthe filter in column D i filter column E which gives me the same results because in the column the criteria matches.
i need to check the column E filtered values with the column D filtered values if this is same then i need to copy only the unmatched values of column E.
Can someone suggest me how to do the above scenario pls..
-Sindhuja
BrianMH
04-24-2012, 12:45 AM
can you post some example data
Bob Phillips
04-24-2012, 03:03 AM
Add a helper column with a formula that checks D OR E = PARK and filter on TRUE in that column.
sindhuja
04-24-2012, 10:33 PM
Thanks for the suggestion Xld. i need to compare the results i received while filtering column D with the results of filtered values in column E.
If this is the scenario, how can i insert a column and compare the values.
It would be great if you provide some more information.
-Regards
Bob Phillips
04-25-2012, 12:41 AM
Something like this
Sub ProcessData()
Dim rng As Range
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .UsedRange.Rows.Count
.Columns("F").Insert
.Rows(1).Insert
.Range("F1").Value = "tmp"
.Range("F2").Value = "FALSE"
.Range("F3").Resize(lastrow - 1).Formula = "=OR(ISNUMBER(FIND(""PARK"",D3)),ISNUMBER(FIND(""PARK"",E3)))"
Set rng = .Range("F1").Resize(lastrow + 1)
On Error Resume Next
rng.AutoFilter Field:=1, Criteria1:="TRUE"
On Error GoTo 0
If Not rng Is Nothing Then
Set rng = rng.SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
End If
.Columns("F").Delete
End With
End Sub
sindhuja
04-26-2012, 04:27 AM
Hi,
I have attached the sample sheet but the results are not expected.
filter criteria is contains "PARK"
i have highlighted the expected results. Please help me :(
-Sindhuja
Bob Phillips
04-26-2012, 07:39 AM
Sub ProcessData()
Dim rng As Range
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .UsedRange.Rows.Count
.Columns("F").Insert
.Rows(1).Insert
.Range("G1").Value = "tmp"
.Range("G2").Value = "FALSE"
.Range("G3").Resize(lastrow - 1).Formula = "=AND(ISNUMBER(SEARCH(""PARK"",D3)),ISNUMBER(SEARCH(""PARK"",E3)))"
Set rng = .Range("G1").Resize(lastrow + 1)
On Error Resume Next
rng.AutoFilter Field:=1, Criteria1:="TRUE"
On Error GoTo 0
If Not rng Is Nothing Then
Set rng = rng.SpecialCells(xlCellTypeVisible)
' rng.EntireRow.Delete
End If
.Columns("G").Delete
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.