Vlookup multiple values separated by semicolon

0
votes

I need help to align same values from two columns where there are more than one code separated by semicolon in one single cell.

I have one column like that:

UMLS CODE
C0443147
C0441748;C4020899
C4025900
C0085606;C3544092;C4020898

And i need to match the following data with the column above.

UMLS CODE  TYPE    MEDDRA CODE         DEFINITION
C0443147    LT;PT   10014275;10014407   EEG;Electroencephalogram
C4020899    LT;PT   10014544;10014430   EMG;Electromyogram
C3544092    OL;LT   10014828;10014449   Electronystagmography
C0013854    PT;LT   10014455;10014359   Electro-oculogram

So the result matching the UMLS CODES column must be like this:

UMLS CODE                  UMLS CODE  TYPE    MEDDRA CODE         DEFINITION
C0443147                   C0443147   LT;PT   10014275;10014407   EEG;Electroencephalogram
C0441748;C4020899          C4020899   LT;PT   10014544;10014430   EMG;Electromyogram
C4025900                   -------    -----   -----------------   -------------------
C0085606;C3544092;C4020898 C3544092   OL;LT   10014828;10014449   Electronystagmography

I tried the following formula on excel but didnt work when the looking value has more than one value separated by semicolon.

=VLOOKUP($A1;$A$13819:$D$63379;COLUMN(A:A);0)

Where $A1 is the UMLS CODE and $A$13819:$D$63379 is all the data to match with UMLS CODE.

Pictures from UMLS codes: enter image description here

Picture from UMLS data: enter image description here

Result desired but working also in multiple value cells separated by semicolon from UMLS code: enter image description here

2
excelexcel-formulacellvlookupmatching
So matching any one UMLS code is satisfactory if there are more than one values separated by semicolon? – shrivallabha.redij
vlookup can't return two results simultaneously, it also won't be able to iterate through the different codes and return the first found in case that is what you are trying to do, you could have nested if statements return the first viable UMLS code and then use a vlookup on that. but in my humble opinion when I smell iterations I think of a simple for loop in vba might want to look at string manipulation and for loops in vba if this is a project you really want to automate. Having said that a formula is fine but would need more specifics. Look at if and middle formulas – Ricards Porins
It have to match all UMLS codes available when having more than one values separated by semicolon in a single cell to be perfect. – S.Ram
@S.Ram to you prefer VBA code? – Error 1004
Is ok to use VBA if works correctly, problem is I am very bad at programming in VBA. @Error1004 – S.Ram

2 Answers

2
votes

assuming you have a maximum number of UMLS codes in your list (I have assumed 3)

The table on row 8-12 is an intermediary step just to simplify this can be put in the final function if you so choose.

=TRIM(MID(SUBSTITUTE($A5;";";REPT(" ";LEN($A5))); (C$8)*LEN($A5)+1;LEN($A5)))

enter image description here

=IFERROR(VLOOKUP($A12;$C$2:$F$5;4;FALSE);IFERROR(VLOOKUP($B12;$C$2:$F$5;4;FALSE);VLOOKUP($C12;$C$2:$F$5;4;FALSE)))

enter image description here

Column A16 is just = A2 etc

1
votes

@S.Ram,

Import the data in Sheet1 as in the picture:

enter image description here

Import the data in Sheet2 as in the picture:

enter image description here

and try:

Option Explicit
Option Explicit

Sub test()

Dim LastRow1 As Long
Dim LastRow2 As Long
Dim i As Long
Dim j As Long
Dim Word As String
Dim Word1 As String
Dim Word2 As String
Dim SpecialChr As Long
Dim Position As Long
Dim Position2 As Long

LastRow1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheet2.Range("B" & Rows.Count).End(xlUp).Row

 With Sheet1

    For i = 2 To LastRow1

        SpecialChr = (Len(.Range("A" & i).Value) - Len(Replace(.Range("A" & i).Value, ";", ""))) / Len(";")

        If SpecialChr = 0 Then
            Word = .Range("A" & i).Value
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    End If
                Next j
            End With
        ElseIf SpecialChr = 1 Then
            Position = InStr(1, .Range("A" & i).Value, ";")
            Word = Left(.Range("A" & i).Value, Position - 1)
            Word1 = Right(.Range("A" & i).Value, Position - 1)
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    ElseIf .Range("B" & j).Value = Word1 Then
                        .Range("B" & j).Offset(0, -1).Value = Word1
                    End If
                Next j
            End With
        ElseIf SpecialChr = 2 Then
            Position = InStr(1, .Range("A" & i).Value, ";")
            Position2 = InStr(Position + 1, .Range("A" & i).Value, ";")
            Word = Left(.Range("A" & i).Value, Position - 1)
            Word1 = Mid(.Range("A" & i).Value, Position + 1, Len(.Range("A" & i).Value) - Position2)
            Word2 = Right(.Range("A" & i).Value, Position - 1)
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    ElseIf .Range("B" & j).Value = Word1 Then
                        .Range("B" & j).Offset(0, -1).Value = Word1
                    ElseIf .Range("B" & j).Value = Word2 Then
                        .Range("B" & j).Offset(0, -1).Value = Word2
                    End If
                Next j
            End With
        End If
    Next i
End With

End Sub

Finnaly, the results will be import in Sheet2 yellow area.