Vlookup Problem

Hi Guys, I have a Vlookup on an Excel Cell containing several IDs. I use the look up on the Previous Day's speadsheet to see what the position is on the trades. See below

15th of Oct
1909843.28, 2222646.28, 2222836.28, 2224190.28, 2224281.28
16th of Oct
2224281.28, 1909843.28, 2222646.28, 2222836.28, 2224190.28
The Problem is the sequence of the trades IDs randomly switches from one day to the next due to a system bug so my Vlookup does not work. Is there a way that irrespective of any change in the order of the trade ID, the Vlookup could still work?
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
I agree with ssaqibh that a formula solution will be difficult.
With 5 ID numbers there are 120 combinations for the numbers position in the string.

Not clear if you have 4 or 5 ID numbers in the string, or if the ID numbers end with .28.
I have made an example file with some test ID numbers with 5 ID numbers in each string, each with 7 digits.
I guess the format is fixed, so it is overkill to make a solution for variable number of ID and variable ID format. Easy changed when the format is known.
The formula is in column A on the sheet Today, looking for a match for column B, on the sheet Previous, column B, with result in column C.

Here is the code for the function

Option Explicit

Function FindPreviousID(x As String) As Variant
    'Application.Volatile makes the function run on every calculation, must be first line in function
    Dim wsPrevious As Worksheet
    Dim RowNo As Long
    Dim arrXvalues(5) As String
    Dim PreviousRecord As String
    Dim i As Integer
    Dim Found As Integer
    Dim MatchID As Integer
    Set wsPrevious = Worksheets("Previous")
    'Put the ID numbers in array
    For i = 1 To 5
        arrXvalues(i) = Mid(x, 9 * (i - 1) + 1, 7)
    Next i
    'Loop the previous day values
    RowNo = 0
    Found = 0
        RowNo = RowNo + 1
        MatchID = 0
        'Get the record from the previous day in column B
        PreviousRecord = wsPrevious.Cells(RowNo, 2)
        'Check if ID number can be found in record
        For i = 1 To 5
            If InStr(1, PreviousRecord, arrXvalues(i)) > 0 Then
                MatchID = MatchID + 1
            End If
        Next i
        'Match if all 5 ID numbers are in the previous record
        If MatchID = 5 Then
            Found = 1
        End If
    Loop Until Found = 1 Or RowNo >= wsPrevious.UsedRange.Rows.Count
    'Result is value in column C, or no result
    If Found = 1 Then
        FindPreviousID = wsPrevious.Cells(RowNo, 3)
        FindPreviousID = "No match"
    End If
End Function

Open in new window

Saqib Husain, SyedEngineerCommented:
I am not clear on your problem. Can you explain more possibly with an example showing how your vlookup works?
JustincutAuthor Commented:
Ok. Its just a standard Vlookup on 1 Excel Cell on the change in position of a shareholding in Esso. The problem is within the Excel Cell are 4 Numbered trade Ids, but they switch in sequence from 1 day to the next so (due to system bug) , if you do a Vlookup on yesterday's position in Esso, it won't work
The Excel Cell on the 15th reads 1909843, 2222646, 2222836, 2224190 2224281

The Excel Cell on the 16th  reads 2224281, 1909843, 2222646, 2222836, 2224190
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Saqib Husain, SyedEngineerCommented:
I am sorry I am still blank. Can you give me the formula you are using? Which is the value looked up, where is it looked up?
JustincutAuthor Commented:
I am using Vlookup(B3, B:C, 2, False) from Previous Day Excel Rec.

Cell B3 contains 4 trade Ids (1909843, 2222646,2224190,2224281)
Cell B3 the next day contains the same 4 ids but in a different order (2224281,2224190,2222646,1909843) thus the Vlookup fails.I need to know
a formula whereby it can still do a Vlookup even if the digits have changed order.
Saqib Husain, SyedEngineerCommented:
That sounds horrible if i have understood you correctly. It may not be impossible but would surely be next to it. I wonder if someone can have a go at it. The only way I can foresee this being done is through VBA and it is not going to look elegant.

Can you build a small file which I can use for experimenting?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.