Check the entire row and delete duplicate values

[sindhuja]

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]

can you post some example data

[Bob Phillips]

Add a helper column with a formula that checks D OR E = PARK and filter on TRUE in that column.

[sindhuja]

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]

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]

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]

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