Follow Up Question from:
The code below works well, but I have since realized that the values in column A sometimes contain a prefix, which therefore returns nothing.
Hoping someone can edit the actually formula to search for exact string matches perhaps instead of number matches?
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
Set ws = Sheets("Main")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH($A2&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
Sample of data in Column A:
The data in the table will also contain prefix IF the value is there.. So essentially, the MATCH is looking for an exact match.
Sample file attached.
I posted here as well:
And the only reply I have received since, is to identify the number of chars in the string, then use that number to split the string and then I am left with only the number without the prefix. The problem is that I believe this might bring up some false positives - The data in the table also contains prefix'd numbers and non-prefix'd numbers, so it is very possible that after the prefix is removed, it might end up matching with the same number in the table but that does not necessarily meant that it is the correct number.
Apart from this, I recon I would need to apply the same method to my table so as to attempt tp match 12356 with 123456 instead of trying to match 123456 with AB123456.