Extracting comma separated multiple values from a range of cells

[mrc44]

I am trying to bring a set of values (either a set of 3 or 4 values) look up in a row of data with unique values in each cell, and if any or all of the values match, extract them to target single cell comma separated if necessary. Any help to achieve this would be appreciated. Thanks. Here the tricky part, the values to look up are present comma separated in a single cell.

 

[igold]

Hi mrc44,

Welcome to the Forum.

Two questions... Are you looking for a formula or a macro, and in your graphic above, is the result in Cell L5 correct (I do not see an "a" in "A5:I5"). Additionally should there be a "c" in cell L6.

 

[mrc44]

Hi mrc44,

Welcome to the Forum.

Two questions... Are you looking for a formula or a macro, and in your graphic above, is the result in Cell L5 correct (I do not see an "a" in "A5:I5"). Additionally should there be a "c" in cell L6.

Hi igold,
I made a mistake in L5, it should be b instead of a. thanks.
If possible with a function, it might be easier for me. but I think I can use the VBA code too if it better suits you.
thanks.

 

[igold]

See if this does what you want. I would think it could be done with a formula but I was not able to come up with it. Perhaps someone else on the Forum will jump in with it. Also, I just noticed that you had said:

if any or all of the values match, extract them to target single cell comma separated if necessary

The commas in the target cell is your choice. If you do not want the results comma separated, that is an easy change to make.

Sub GetValues()


    Dim sLine As Variant
    Dim rslt As String
    Dim lRow As Long, i As Long, x As Long
    Dim a As Integer
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sLine = Split(Range("L1"), ",")
    For x = 3 To lRow
        For i = LBound(sLine) To UBound(sLine)
            For a = 1 To 9
                If Cells(x, a).Value = sLine(i) Then
                    rslt = rslt & Cells(x, a).Value & ","
                End If
            Next
        Next
        Cells(x, 12).Value = Left(rslt, Len(rslt) - 1)
        rslt = Empty
    Next
            
End Sub

I hope this helps.

 

[igold]

There is an omission in the above code, please try this one instead...

Sub GetValues()


    Dim sLine As Variant
    Dim rslt As String
    Dim lRow As Long, i As Long, x As Long
    Dim a As Integer
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sLine = Split(Range("L1"), ",")
    For x = 3 To lRow
        For i = LBound(sLine) To UBound(sLine)
            For a = 1 To 9
                If Cells(x, a).Value = sLine(i) Then
                    rslt = rslt & Cells(x, a).Value & ","
                End If
            Next
        Next
        On Error Resume Next
        Cells(x, 12).Value = Left(rslt, Len(rslt) - 1)
        rslt = Empty
        On Error GoTo 0
    Next
            
End Sub

Sorry about the confusion...

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub GetValues()
  Dim R As Long, V As Variant, Txt As String
  For R = 3 To Cells(Rows.Count, "A").End(xlUp).Row
    Txt = ""
    For Each V In Split([L1], ",")
      If Not Intersect(Rows(R), Columns("A:I")).Find(V, , , xlWhole, , , False, False) Is Nothing Then Txt = Txt & "," & V
    Next
    Cells(R, "L").Value = Mid(Txt, 2)
  Next
End Sub[/td]
[/tr]
[/table]

 

[mrc44]

I am sorry but how do I run this code in excel?

There is an omission in the above code, please try this one instead...

Sub GetValues()


    Dim sLine As Variant
    Dim rslt As String
    Dim lRow As Long, i As Long, x As Long
    Dim a As Integer
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sLine = Split(Range("L1"), ",")
    For x = 3 To lRow
        For i = LBound(sLine) To UBound(sLine)
            For a = 1 To 9
                If Cells(x, a).Value = sLine(i) Then
                    rslt = rslt & Cells(x, a).Value & ","
                End If
            Next
        Next
        On Error Resume Next
        Cells(x, 12).Value = Left(rslt, Len(rslt) - 1)
        rslt = Empty
        On Error GoTo 0
    Next
            
End Sub

Sorry about the confusion...

 

[Rick Rothstein]

I am sorry but how do I run this code in excel?

You can run igold's or my macros using the following...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetValues) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[igold]

Hi,

See if this link helps you get the macro into your workbook and then be able to run it. For what it is worth, Rick's code is more efficient than mine.

Insert and run VBA macros in Excel 2010, 2013 - step-by-step guide

I hope this helps

 

[mrc44]

Basically, I know how to insert VBA code in Excel and run it. What I meant to ask was how I can run this code in a specific sheet and cells that I want the results to be displayed. I noticed in your code sLine = Split(Range("L1"), ",") this line, does it mean that this code is specifically has to be run on L1 column? because I have several columns and rows to run.