Excel VBA 2 similar values not detected as matching

Question

Sample Excel

Hi Guys,

I am having a bit of trouble with my excel macro. There's a part of code wherein it compares cells from sheet1 to sheet2. The issue is even when a cell in sheet1 matches the cell in sheet2 it still shows as "NOT MATCH".

I believe that one of the cells, either ValueA or ValueB, has hidden characters in it. Their contents came from different sources. I tried doing CLEAN() formula but that didn't help. Any ideas? PLEASE SEE LINKED FILE ABOVE. I copied and pasted the 2 values AS IS so you can test what could be hidden.

I am dealing with a large rows of data so I don't want to be using DO WHILE in VBA. Is there a formula or code to leave just visible characters and SPACE in a cell?

================================================================

Sample2

Here's another sample, this is taken from the master file itself so it's close to the original. As you can see, there is a formula in sheet1 column B that checks if each of the Record ID already exists in sheet2. All of them say "NOT FOUND" (Doesn't exist in sheet2) but many of them actually already exists in sheet2 including Record ID "1743840". If you do a manual CTRL+F to find that Record ID in sheet2 you'll see it's there and the FIND system works, but the formula isn't.

Answer 1

Try replacing your lookup value with IFERROR(VALUE($A2),$A2)

So, your formula becomes

=IF(IFERROR(MATCH(IFERROR(VALUE($A2),$A2),OldFile!A:A,0),"NOT FOUND")<>"NOT FOUND","FOUND","NOT FOUND")

Answer 2

Hmmm. Your example does not have a macro, it has a formula.

One way to eliminate the text/number issue would be to use the EXACT worksheet function (which works on numbers as well as on text strings).

e.g.:

=IF(EXACT(B3,C3),"Same","Not Same")

If you want to make it case insensitive, use UPPER to change text strings to all upper case.

Hmmm, based on your new example, a simple UDF would do the trick. It is mimicking the "Find" method you used manually. I made it case sensitive, but you can easily change that. The below returns true or false, depending on whether there is a match. R is the range to search, so it could be OldFile!$A$2:$A$160

Function IsFound(S As String, R As Range) As Boolean
    With R
        If Not .Find(what:=S, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
            IsFound = True
        Else
            IsFound = False
        End If
    End With
End Function

Answer 3

In your attached sheet, the value in B3 is stored as text while the value in cell C3 is a numeric, so technically, they aren't the same. You can get around that by changing your formula in D3 to be something like:

=IF(B3+0=C3+0,"Same","Not Same")

the +0 forces the text to be interpreted as a numeric. You can apply this same logic to your larger sheet by adding 0 to the numeric values that have been stored as text.

To convert the text to numbers quickly, here is one way using VBA (without knowing anything about the layout of your data):

Sheet1.Range("B1").Copy
Sheet1.Range("B2:B2000").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

This assumes B1 is a blank cell and the numbers that are stored as text are in B2:B2000. You'll have to adjust the range to suit your situation or post more details about the layout of your data.

Answer 4

Pertinent to your case, assuming you compare the values in cell A1 of Sheet1 and value of cell A1 of Sheet2. The good starting point will be to try Excel worksheet function like: =(A1=Sheet2!A1) entered in cell A2 of Sheet1 and see the result. Based on findings, proceed either with worksheet functions, or VBA (upon necessity). In case the cell formats are different (e.g. one is formatted as text, another as number), convert both to text and apply trim() function (just in case). Rgds,