Suggestion for better search algorithm please

deadseasquirrels

Board Regular
Joined
Dec 30, 2004
Messages
232
Often times I have to go through a spreadsheet and delete doubles (rows are the same or similiar). The method I use right now is something like this:
Code:
        With Row1
            columnSum1 = .Cells(1, "B") + .Cells(1, "C") + .Cells(1, "E") + _
                        .Cells(1, "F") + .Cells(1, "G") + .Cells(1, "I") + _
                        .Cells(1, "J") + .Cells(1, "K") + .Cells(1, "L") + _
                        .Cells(1, "N") + .Cells(1, "P") + .Cells(1, "U") + .Cells(1, "AE")
        End With
    
        With Row2
            columnSum2 = .Cells(1, "B") + .Cells(1, "C") + .Cells(1, "E") + _
                        .Cells(1, "F") + .Cells(1, "G") + .Cells(1, "I") + _
                        .Cells(1, "J") + .Cells(1, "K") + .Cells(1, "L") + _
                        .Cells(1, "N") + .Cells(1, "P") + .Cells(1, "U") + .Cells(1, "AE")
        End With
    
        If StrComp(columnSum1, columnSum2) = 0 Then
            Row1.Interior.ColorIndex = FILL_COLOR
            Row2.Interior.ColorIndex = FILL_COLOR
            deleteRow Row1, Row2, i
        End If

Which just adds up some particular cells in the row and compare it to the one above or below it (after it is sorted of course). If the two rows are the same, then I'll delete one of them. Then I'll go through the whole sheet row by row doing this (from the bottom up). I was just wondering if there is a better way to find doubles. For example if I just wanted to find all the rows that had the person named John Doe, is there an easy way to say find all the things are that are both John and Doe and return a range of that. Thanks for the help.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,123,122
Messages
5,599,830
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top