function/formula like Ctrl+F -> find all in one cell?

[ogustuce]

hi, i've done hours of ressearch and i can't find what im looking for. I've seen stuff like Vlookup, Index, etc. they all don't work because you need to have the same keyword repeated in your list which i do not have.

What i need to do is search for a keyword and write all the results in a single cell.

Logic:
keyword "*apple*"

list:
applez
apples
brown
yellow
zapples
trapples

result of formula/function would be:
applez,apples,zapples,trapples


i dont mind VBA if its a function or a formula


thank you

 

[DanteAmor]

Try this function

<b>sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:207.21px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">keyword</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Result</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">List</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >apple</td><td >applez, apples, zapples, trapples</td><td > </td><td >applez</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td >apples</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td >brown</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td >yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td >zapples</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td >trapples</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Function</td></tr><tr><td >B2</td><td >=find_all_in_one(A2,D2:D7)</td></tr></table></td></tr></table> <br /><br />

Function find_all_in_one(cell As Range, list As Range)
    cad = ""
    For Each c In list
        If LCase(c.Value) Like "*" & LCase(cell.Value) & "*" Then cad = cad & ", " & c.Value
    Next
    If cad <> "" Then find_all_in_one = Mid(cad, 3) Else find_all_in_one = ""
End Function

 

[ogustuce]

thank you very much sir!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[ogustuce]

Try this function

sheet1

	A	B	C	D
apple	applez, apples, zapples, trapples		applez
			apples
			brown
			yellow
			zapples
			trapples

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:207.21px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]keyword[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]List[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Cell Function B2 =find_all_in_one(A2,D2:D7) <tbody> </tbody>

<tbody>
</tbody>

Function find_all_in_one(cell As Range, list As Range)
    cad = ""
    For Each c In list
        If LCase(c.Value) Like "*" & LCase(cell.Value) & "*" Then cad = cad & ", " & c.Value
    Next
    If cad <> "" Then find_all_in_one = Mid(cad, 3) Else find_all_in_one = ""
End Function

this worked great, now can you please add a line that would take the value in the cell next to where it finds the result? i really like that your code isn't case sensitive in the search. all the other functions/formulas i find dont work even when i add "*"& &"*"

 

[DanteAmor]

this worked great, now can you please add a line that would take the value in the cell next to where it finds the result?

If I understood correctly do you want to put a mark in cells E2, E3, E6 and E7?

That is not possible, we would have to change from a function to a macro

 

[ogustuce]

If I understood correctly do you want to put a mark in cells E2, E3, E6 and E7?

That is not possible, we would have to change from a function to a macro

well just like Vlookup do.

logic:

keyword: apple

A B
1 applez 34
2 Zapples 43
3 yellow 22
4 cow 33
5 Mapples 4
6 purple 5


then with your superb function: =find_all_in_one(apple,A:B)

result: 34, 43, 4

 

[DanteAmor]

Use this.

Col = 2

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:207.21px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">keyword</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Result</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">List</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >apple</td><td >34, 43, 4, 5</td><td > </td><td >applez</td><td style="text-align:right; ">34</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td >apples</td><td style="text-align:right; ">43</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td >brown</td><td style="text-align:right; ">22</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td >yellow</td><td style="text-align:right; ">33</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td >zapples</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td >trapples</td><td style="text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Function</td></tr><tr><td >B2</td><td >=find_all_in_one(A2,D2:E7,2,TRUE)</td></tr></table></td></tr></table> <br /><br />


Col = 1

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet</b></td></tr><tr><td ></td></tr></table> <br /><br /><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:207.21px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">keyword</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Result</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">List</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >apple</td><td >applez, apples, zapples, trapples</td><td > </td><td >applez</td><td style="text-align:right; ">34</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td >apples</td><td style="text-align:right; ">43</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td >brown</td><td style="text-align:right; ">22</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td >yellow</td><td style="text-align:right; ">33</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td >zapples</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td >trapples</td><td style="text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=find_all_in_one(A2,D2:E7,1,TRUE)</td></tr></table></td></tr></table> <br /><br />