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
GBO ID
1909843.28, 2222646.28, 2222836.28, 2224190.28, 2224281.28
16th of Oct
GBO ID
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?
JustincutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
I am not clear on your problem. Can you explain more possibly with an example showing how your vlookup works?
0
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
0
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?
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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.
0
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?
0
Ejgil HedegaardCommented:
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
    '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
    Do
        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)
    Else
        FindPreviousID = "No match"
    End If
End Function

Open in new window

Special-Vlookup-function.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.