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

Saltysteve

New Member
Joined
Jul 23, 2014
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 23, 2014
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 23, 2014
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Well, at least the mystery is now solved, and you know it wasn't a problem with your formulas!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,985
Messages
5,575,381
Members
412,658
Latest member
LS0009
Top