Excel Not Recognizing Cells of Equal Value

[G_Money]

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.

 

I am completely stumped...some one please help!

 

Cheers,

 

Greg

[Kained]

If the cells are numeric cells set them to numeric

 

select all your cells right click format cells then select number from the list and try the comparison again.

 

failing that if they are text values do the same as above but change it from general to text.

 

hope that helps

[apeo]

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:

 

Sub DeleteDuplicates()
   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
               Selection.Delete Shift:=xlUp
           End If
       End If
       i = i + 1
   Loop

End Sub

 

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.