none
Extracting comma separated multiple values from a range of cells RRS feed

  • Question

  • 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.

    With 'Unique', I meant values in each row would be different. no repeating values in the same row. As an example, if there is a cell with comma separated 3 values like (a,b,c), this would be looked up in the data row, and if any or all of them is present in the row, they would be extracted to another single cell. if there is one value, it would be (a), two values (a,b), three values (a,b,c) extracted to the target cell comma separated. I am sorry, but the site doesn't let me post images or links yet. 

    Thanks.


    Sunday, August 13, 2017 11:33 AM

Answers

  • Hi Mrc33,

    You could try below could and adjust it for your indeed.

    Sub Test()

    startCol = 11

    EndCol = 13

    'EndCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

    StartRow = 5

    EndRow = 7

    'EndRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    Dim arr As Variant

    Dim dataRng As Range

    For i = StartRow To EndRow

         Set dataRng = Range(Cells(i, 1), Cells(i, 8))

         dataRng.Select

         For j = startCol To EndCol

         valueToLookUP = Cells(1, j).Value

         arr = Split(valueToLookUP, ",")

         resultStr = ""

         For k = LBound(arr) To UBound(arr)

         On Error Resume Next

         idx = WorksheetFunction.Match(arr(k), dataRng, 0)

         If idx > 0 Then

         resultStr = resultStr + "," + arr(k)

         End If

         idx = 0

         Next k

         If Len(resultStr) > 0 Then resultStr = Mid(resultStr, 2)

         Cells(i, j).Value = resultStr

         Next j

    Next i

    End Sub

    Best Regards,

    Terry

    • Marked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    Monday, August 14, 2017 2:11 AM
  • Hi Mrc33,

    I've shared my sample file "Find_Match_ash.zip" via OneDrive.
    https://1drv.ms/u/s!AhzOJeY5F3-fjZxU5dCaGfPTaVAbyA
        

    Please download and try it.
    (click [Smart Search] button)

    I didn't do completely what you want, i.e. I've left something undone.
    It's up to you: add For-loop in order to get result of column [L]-[T].
    I suppose it would not be hard to achieve.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Monday, August 14, 2017 2:54 AM
    • Marked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    Monday, August 14, 2017 2:46 AM

All replies

  • 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.

    Thanks.

    Sunday, August 13, 2017 2:03 AM
  • 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.

    Thanks.

    Post examples of what the Excel data would look like.  'Unique' compared to what?  What would you 'match' the value to?  

    How are you wanting to store the extracted data - separate fields, separate records, etc (Access does not have 'cells').


    Build a little, test a little

    Sunday, August 13, 2017 3:53 AM
  • I am sorry, I posted the question in the wrong section. This was an excel question. With 'Unique', I meant values in each row would be different. no repeating values in the same row. As an example, if there is a cell with comma separated 3 values like (a,b,c), this would be looked up in the data row, and if any or all of them is present in the row, they would be extracted to another single cell. if there is one value, it would be (a), two values (a,b), three values (a,b,c) extracted to the target cell comma separated. I am sorry, but the site doesn't let me post images or links yet. 


    • Edited by Mrc33 Sunday, August 13, 2017 11:23 AM
    Sunday, August 13, 2017 11:22 AM
  • I prefer one figure to many words.  Could you make one picture and insert it on your post?

    Ashidacchi


    • Edited by Ashidacchi Sunday, August 13, 2017 12:32 PM
    Sunday, August 13, 2017 12:31 PM
  • I totally agree with you, I tried but this site doesn't let me post images or give links. 
    Sunday, August 13, 2017 12:58 PM
  • You can share a file/an image via cloud storage such as OneDrive, Dropbox, ect.

    Ashidacchi

    Sunday, August 13, 2017 1:02 PM
  •  https://1drv.ms/i/s!AoGkZUHlKui9gQIO5QD7s73rXVbD

    I have these sample data in A3:I3 (a,b,c,d,e,f,g,k,q); A4:I4(a,e,f,b,k,l,m,t,p);A5:I5(b,x,v,z,t,k,m,p,y); A5:I5(d,e,c,p,k,q,t,r,z). and from this data I have a sample values in cell L1 (a,b,c). I need these 3 values (sometimes 4), to be searched in the sample data, and if any or all of them is present in a data row, they should be extracted to the correcponding cell in column L. (L3,L4,L5 etc). So L3 may have (a,b,c), L4 has (a,b), L5 has (b). 

    • Edited by Mrc33 Sunday, August 13, 2017 1:31 PM
    Sunday, August 13, 2017 1:19 PM
  • Hi Mrc33,

    Thank you for sharing an image. That's just what I wanted.
    I'll try to make a sample code tomorrow (it's midnight in Japan).

    Regards,

    Ashidacchi

    Sunday, August 13, 2017 2:13 PM
  • Thanks. Good night.
    Sunday, August 13, 2017 3:39 PM
  • Here I updated the image.

    https://1drv.ms/i/s!AoGkZUHlKui9gQSQgYzQfD6hJBzz

    Also a sample excel file :  https://1drv.ms/x/s!AoGkZUHlKui9gQMQRkAMYH5ry3xk

    I need to remind you that this is just an example image and excel file. 

    Sunday, August 13, 2017 7:24 PM
  • Hi Mrc33,

    You could try below could and adjust it for your indeed.

    Sub Test()

    startCol = 11

    EndCol = 13

    'EndCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

    StartRow = 5

    EndRow = 7

    'EndRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    Dim arr As Variant

    Dim dataRng As Range

    For i = StartRow To EndRow

         Set dataRng = Range(Cells(i, 1), Cells(i, 8))

         dataRng.Select

         For j = startCol To EndCol

         valueToLookUP = Cells(1, j).Value

         arr = Split(valueToLookUP, ",")

         resultStr = ""

         For k = LBound(arr) To UBound(arr)

         On Error Resume Next

         idx = WorksheetFunction.Match(arr(k), dataRng, 0)

         If idx > 0 Then

         resultStr = resultStr + "," + arr(k)

         End If

         idx = 0

         Next k

         If Len(resultStr) > 0 Then resultStr = Mid(resultStr, 2)

         Cells(i, j).Value = resultStr

         Next j

    Next i

    End Sub

    Best Regards,

    Terry

    • Marked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    Monday, August 14, 2017 2:11 AM
  • Hi Mrc33,

    I've shared my sample file "Find_Match_ash.zip" via OneDrive.
    https://1drv.ms/u/s!AhzOJeY5F3-fjZxU5dCaGfPTaVAbyA
        

    Please download and try it.
    (click [Smart Search] button)

    I didn't do completely what you want, i.e. I've left something undone.
    It's up to you: add For-loop in order to get result of column [L]-[T].
    I suppose it would not be hard to achieve.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Monday, August 14, 2017 2:54 AM
    • Marked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    Monday, August 14, 2017 2:46 AM
  • Hi Mrc33,

    How is your issue?  Have you resolved it?

    Ashidacchi

    • Marked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    • Unmarked as answer by Mrc33 Monday, August 14, 2017 9:17 AM
    Monday, August 14, 2017 8:58 AM
  • Thanks a lot! Buttons are a very nice touch. It works perfectly!!
    Monday, August 14, 2017 9:11 AM
  • Thank you for your suggestion. I will try that. 
    Monday, August 14, 2017 9:12 AM