If list A has an item from list B create list C

[aamarb]

Hi Guys,

I receive a list of items pertaining a customer. I want to do is check if list A has an item from list B then create list C. The problem I’m having is that list A is all in one cell separated by a carriage return. So for example cell A3 has xxx xxy xxz, and can be up to 40 items. I want to compare it to list B which is on sheet 2 column A and if it is in list B show all matching items in cell C3, comma separated or carriage separated.

Many thanks in advance

 

[jasonb75]

This formula uses the TEXTJOIN function which is not supported by older versions of excel.

=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH(CHAR(10)&B3:B40&CHAR(10),CHAR(10)&A3&CHAR(10))),B2:B40,""))

Note that this is an array formua which must be confirmed by pressing Ctrl Shift and Enter, not just Enter.

 

[Fluff]

How about

Function aamarb(St1 As String, Rng As Range) As String
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   With CreateObject("scripting.dictionary")
      For Each Cl In Rng
         .item(Cl.Value) = ""
      Next Cl
      Sp = Split(St1, Chr(10))
      For i = 0 To UBound(Sp)
         If .exists(Sp(i)) Then aamarb = aamarb & Sp(i) & Chr(10)
      Next i
   End With
   aamarb = Left(aamarb, Len(aamarb) - 1)
End Function

Used like
=aamarb(A3,Sheet2!A2:A100)

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...em-from-list-b-create-list-c.html#post5184753
https://www.ozgrid.com/forum/forum/...-list-a-has-an-item-from-list-b-create-list-c

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[aamarb]

Cross posted https://www.excelforum.com/excel-fo...em-from-list-b-create-list-c.html#post5184753
https://www.ozgrid.com/forum/forum/...-list-a-has-an-item-from-list-b-create-list-c

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Apologies, will remember for next time

 

[Fluff]

Have you tried either of the suggestions posted here?

 

[aamarb]

Solution has been found on excel forums, many thanks for all your help

 

[aamarb]

Please close the thread, can’t find the option on mobile version of website

 

[jasonb75]

Ignore this, advising OP about cross posting, didn't see that Fluff had already raised this.