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.

 

[mrc44]

My understanding is that these VBA codes are good for the sample data in the image I provided. The sample image was a small data group, and there are more columns than L.

 

[igold]

We can only write code based off of the information that you provide. If your real data does not mimic what you provided there is no way we can possible write code that will be useful to you, unless you have the ability to alter the code based on the real data.

Please provide a representative sample of what the data is.

 

[mrc44]

I am sorry about the confusion. I insert another image with more data and explanation. Please let me know if anything needs to be clarified.


The Image: https://1drv.ms/i/s!AoGkZUHlKui9gQSQgYzQfD6hJBzz ( I am sorry, I couldn't insert this link).

Sample Excel workbook: https://1drv.ms/x/s!AoGkZUHlKui9gQMQRkAMYH5ry3xk

 

[Rick Rothstein]

I am sorry about the confusion. I insert another image with more data and explanation.

As igold said, if you don't show us what your data layout is and what you want from it, there is no way we could possibly guess at either. This picture you posted in Message [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 3[/URL] should have been posted in Message #1 . Anyway, here is my code modified to do what you are now asking for...

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

 

[mrc44]

Thanks Rick, and igold. This works perfectly. The reason I didn't share the original data, there were too much of it, and different workbooks. So I need to modify the position of rows, columns according to your code. It is a little slight arrangement, so it won't be a problem. Thanks a lot!!

 

[Rick Rothstein]

Thanks Rick, and igold. This works perfectly. The reason I didn't share the original data, there were too much of it, and different workbooks.

There is never a need to post all of your data, only a representative sample of it. In other words, don't show us a single cell when you actually have a row or column of data (the code for handling either is different), don't show us numbers when your cells contain combinations of number and text (the code for handling either is different), don't show us the data you want to process is in Columns A, B, C, etc. when actually it is located in non-contiguous columns like A, D, M, etc. (the code for handling either is different), and so on. We can only design solution for what you tell us you have, don't assume we are mind readers and will divine your actual layout.

So I need to modify the position of rows, columns according to your code. It is a little slight arrangement, so it won't be a problem. Thanks a lot!!

If you would have told us where your data is actually located at, we would have designed our solutions for it directly

 

[mrc44]

I am sorry for the confusion and misunderstanding really. Thanks for your suggestions, I will be more careful next time.

 

[mrc44]

I am sorry for the confusion and misunderstanding that I caused. Thanks for your suggestions, I will be more careful next time.

 

[igold]

I did not get a chance to look at your new post, but I am happy that Rick was able to help you out. Thanks for the feedback.