Vlookup on Multiple Values in a Cell 3

[gmoorthy]

I have an excel spreadsheet (A)where the are multiple data in a cell as given below



ABC:1.1
DEF:1.1.2
XYZ:1.1.3

In another spreadsheet(B) i have two columns which has the values as given below

Column A Column B

ABC 1.1
DEF 1.1.2

How can i do a vlookup or any other lookup so that from spreadsheet B if the values get a hit ( combination of Column A + B) on spreadsheet A then i can pick additional column from SpreadSheet A.

 

[lionelhill]

yes, in vlookup, instead of vlookup(A1, myrange, colno, false) use vlookup(A1&B1, myrange, colno, false)

One proviso is that unless you are careful, 1.1 in column B will be interpreted as a number, not text, in which case vlookup will go weird.

 

[SkipVought]

...and in conjunction with lionelhill's caution, faq68-6659.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

Thanks for the reply and this was helpful , but the issue is in my Spreadsheet A the values are in one single cell for example cell A1 has multiple values

ABC:1.1
DEF:1.1.2
XYZ:1.1.3


will the vlookup formula that you gave will work in that instance ?

 

[SkipVought]

Are you saying that
[tt]
ABC:1.1
DEF:1.1.2
XYZ:1.1.3
[/tt]
is in one cell?

How many rows do you have like this?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

I have around 1000 rows like this

 

[SkipVought]

There were TWO questions I asked, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

Sorry missed that one

Yes

ABC:1.1
DEF:1.1.2
XYZ:1.1.3

are in one cell

 

[SkipVought]

Assuming that you 1) have a blank sheet named Sheet2 and 2) SELECT all the cells containing these multiple values on your sheet, paste this code into a module in your VB Editor (alt+F11 toggels between the sheet and VBE), and RUN the procedure, it will load Sheet2 with single rows that are consistent with your target data...

Sub ParseData()
    Dim r As Range, i As Integer, a, j As Integer, b, lRow As Long
    
    lRow = Sheets("Sheet2").[A1].CurrentRegion.Rows.Count + 1
    For Each r In Selection
        '[b]parse the LINE FEEED[/b]
        a = Split(r.Value, vbLf)
        For i = 0 To UBound(a)
            '[b]parse the COLON[/b]
            b = Split(a(i), ":")
            For j = 0 To UBound(b)
                '[b]write parsed values to Sheet2[/b]
                Sheets("Sheet2").Cells(lRow, j + 1).Value = Trim(b(j))
            Next
            lRow = lRow + 1
        Next
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

That works thanks a lot , can i ask another question. As i said earlier my single cell has multiple values , but there are other columns (B to Z) associated with this single cell having multiple values is it possible to copy the rest of columns repeatedly when you split the values so that i can do a vlookup for ex:

Note: Just keeping in mind that Cell A1 , A2 .... could have multiple values but B1 , C1 will have only one value

Column A Column B Column C

ABC:1.1 Yes No
DEF:1.1.2
XYZ:1.1.3


I would need it as follows

ColumnA Column B ColumnC Column D

ABC 1.1 Yes No
DEF 1.1.2 Yes No
XYZ 1.1.3 Yes No

 

[SkipVought]

This ought to do it...

Sub ParseData()
    Dim r As Range, i As Integer, a, j As Integer, b, lRow As Long
    
    lRow = Sheets("Sheet2").[A1].CurrentRegion.Rows.Count + 1
    For Each r In Selection
        '[b]parse the LINE FEEED[/b]
        a = Split(r.Value, vbLf)
        For i = 0 To UBound(a)
            '[b]parse the COLON[/b]
            b = Split(a(i), ":")
            For j = 0 To UBound(b) + 2 '[b]2 extra columns[/b]
                '[b]write parsed values to Sheet2[/b]
                With Sheets("Sheet2").Cells(lRow, j + 1)
                    .NumberFormat = "@"  '[b]format ALL TEXT[/b]
                    Select Case j
                        Case Is <= UBound(b)
                            .Value = Trim(b(j)) '[b]parsed values[/b]
                        Case Else
                            .Value = r.Offset(0, j - UBound(b)) '[b]extra columns[/b]
                    End Select
                End With
            Next
            lRow = lRow + 1
        Next
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Probably much better to sort out the data using Skip's solution but...

yes, in vlookup, instead of vlookup(A1, myrange, colno, false) use vlookup(A1&B1, myrange, colno, false)

As above with wildcards:
vlookup([red]"*"&[/red]A1&B1[red]&"*"[/red], myrange, colno, false)


Gavin