I have been trying to help remove duplicate records from a large list of records using excel. The records were orginally part of two separate csv files, which were combined once they had been converted to excel format. The problem is that when I try to compare values that look the same but came from a different list, excel says they are not equal.
For example, I have excel looking to see if the last name in the next record (row) is the same as the last name of the current record (row) ["=if(d2=d3,1,0)] So if the last names are the same it should return a 1 and if they are different a 0. For some reason when cells which have the same value in them (e.g. "Smith") but each is from a different inital list, the formula says the cells' values are different; and the formula works fine if the two rows come from the same initial list.
I have checked and there are no leading or trailing spaces in the cells, there are no special characters in any of the cells, and I have tried saving the excel file into csv and converting it back to excel in order to remove any weird formating issues. I have even tried using different versions of excel with no success.
Wow i didnt know we had members from the US Ok im not really sure whats going on with your spreadsheet but if you could post it i could have a look at it.
I have a macro that i use to remove duplicates for some of my sims reports:
Dim i, j
i = 2
Do While Range("E" & i).Value <> ""
j = i - 1
If Range("F" & i).Value <> "" Then
If Range("F" & i) = Range("F" & j) Then
Range("A" & i, "F" & i).Select
i = i + 1
NOTE: This code will compare 2 cells to see if they are the same and if they are it will delete it. It will continue to do this till it hits a blank cell, which should be the end.