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.

 

[Joe4]

VLOOKUP requires that the data types of the values you are mathing on MUST be the same (i.e. Text matching Text, or Numbers matching Numbers).
Since you have a mixture of numeric entries along with text entries, you are experiencing failure.

Your best bet is to change the column of your data to all text entries.
An easy way to do that is to select that column, go to "Text to Columns" (found under the Data menu), go to Step 3, select the Text option, and click Finish.
Now all the entries in that column with be Text.
(If you need this in VBA code, just turn on the Macro Recorder, and record yourself performing the steps above. Then stop the Recorder, and copy/paste that code into your procedure.)

Then you just need to be sure that the value you are trying to look up is also text. If it is being entered into some specific cell, just format that cell to the Text format before data entry (changing it afterwards will not change the data of data already entered into it).

 

[Saltysteve]

Hi Joe4,

Thanks for your help.
I have tried converting all to text with no luck. The following link is to the spreadsheet with the two columns giving me trouble. Not sure if the macros are with it, but the code I used is in my first post. You will see that where I swapped the ID's for numbers, all 3 methods work. I used find and replace and changed each letter to a number, a to 1, b to 2 etc. thinking that might be a solution except some weird stuff happened to the final results and added 0s to some, converted some to scientific notation and otherwise changed some of the values differently in each sheet. Also not too sure if that won't create some duplicates that should'nt be. Know any other tricks for converting the data to something readable?

Test.xlsm

1drv.ms

 

[Joe4]

I am looking at your sheet, and I am very confused. Can you just explain exactly what it is that you are ultimately trying to accomplish?

 

[Saltysteve]

This is just an extract of the data, there are many other columns in each sheet and several other sheets. I'm afraid I cannot give you the actual workbook as it contains sensitive and personal information. The workbook is a report that is generated from a an external source's database and provided to me. I need to do a fair bit of manipulation of the raw data in order to get the final information I need. The common link in all of the worksheets, except Sheet 2, is the 'code' (information starting with 'VES-'). Both sheets 1 and 2 have the ID within hyperlinks so to get the ID as you see it I use the Mid function. It is only columns F and G in Sheet 1 and J and L, N or P in Sheet 2 that matter.
Hope that explains it ok and thanks for persevering.

 

[Joe4]

So, I don't quite understand the need for all this manipulation. It is one thing to extract part of a string, it is another to start replacing characters, and changing formats.
Are you trying to match it to something? If so, what do each of the values you are trying to match look like initially?

 

[Saltysteve]

As all of the other worksheets include a column with the 'VES' code, I can extract the data I need for my reports using it to match with. Sheet 2 doesn't have the 'VES' code but I need to add it so I can extract information from that sheet also. The only way I can do it is by matching the IDs that Sheet 1 and Sheet 2 both have (other sheets don't). The IDs in each sheet are generated by formulas that access an external database, the results of those formulas are as follows:

Sheet 1 - https://address_removed/#entity_package_overview:entityId%253D60851f23-daf2-e311-a0a1-00155dfc6f05%252CentityMode%253D. The formula used to extract the ID code is =MID(Query1[@Hyperlink],64,8) where Hyperlink is the Header name for the column contained with the table

Sheet 2 - https://address_removed/#activity_packages:activityId%253D765ad216-7ae3-41da-b24d-329069410ef3. The formula used to extract the ID code is =MID(Query2[@Hyperlink],60,8) again where Hyperlink is the Header name for the column contained within the table.

Each ID is actually 36 characters and I have shortened them to the first 8.

It has just occurred to me that the data in each worksheet is contained within a table. Would that affect anything?

 

[Joe4]

OK, if you are uisng MID functions, it will always return TEXT (string) values, even if it is only returning numbers.
So there shouldn't be a need for any extra manipulation, and you should be able to match the values between the two sheets, as it should be comparing Text to Text.

 

[Saltysteve]

You would think so, yet here I am

 

[Joe4]

You would think so, yet here I am

Can you post an example, before you go doing any extra manipulation, that is not working for you? Just one specific one, which looks like should be working, but is not?
The sheet you posted a few posts back seems to have a lot of extra stuff going on in there. Let's try to narrow it down to just specific example, and see if we cannot figure out what is going on.