Searching 2 columns of data that contain both text and numbers. Vlookup, Index(Match and VBA code dont work

[Saltysteve]

Hi all,

I have 2 columns of data contain ID numbers, which include text and numbers, although some are numbers only as follows

d3856609

3b758509

3d758509

3758509​

40194854​

77ab49a9

5903c2ce

Sorry can't adjust the column width, so the numbers are way over there on the right.

I have tried VLOOKUP (using varying match types) , INDEX(MATCH) and VBA but none will work, I think because of the formatting issues. The following code does work when I swap some of the IDs to just numbers.

Sub Test()

'Declare variables
Dim c, d
Dim Calcs, Data As Worksheet

'Name variables
Set Data = Sheets("Sheet1")
Set Calcs = Sheets("Sheet2")

d = Data.UsedRange.Rows
c = Calcs.UsedRange.Rows

For i = 2 To UBound(c) 'Sets the row in the Calcs sheet

For ii = 2 To UBound(d) 'Sets the row in the Data sheet

If Calcs.Cells(i, 10) = Data.Cells(ii, 6) Then Calcs.Cells(i, 12).Value = Data.Cells(ii, 7)
Next ii
Next i

End Sub

Does anyone know a work around for this? Sheet 1 contains 4500 rows of data and Sheet 2, 350

Look forward to any help that can be offered.

 

[Saltysteve]

Thanks for your tenacity. Following link takes you to a cleaned up version of the sheet with a lot of the rows removed also.

Test.xlsm

 

[Joe4]

In looking at your sample file, I cannot find a single ID on Sheet2 that matches any of the IDs on Sheet1.
I put all the IDs together in a list and sorted it, and could find any matches when visually going through the entire list.
Based on that sample file, can you give me an example of one that matches? Tell me the row number on Sheet1 that matches a specific row number on Sheet2.

 

[Saltysteve]

I am astounded by that. Firstly that a expected there to be matches and didn't check that myself first and secondly that there are no matches. I am so sorry to have wasted your time with this. I will need to raise it with the company that provides the data as there definitely should be matches.

Again, so sorry.

 

[Joe4]

Well, at least the mystery is now solved, and you know it wasn't a problem with your formulas!

 

[Saltysteve]

Thanks again Joe

 

[Joe4]

You are welcome.