Identifing matching text within cells

[rsw2011]

Hi all,

I'd be so grateful if someone could help me with this. Would literally save me DAYS not hours per week at work.

I have a spreadsheet with two columns.

Column A = Brand one, Brand two, Brand three
Column B = Brand two

I want
Column C to = Any text that appears in COLUMN A AND COLUMN B. In this example column C should just read Brand two.

I need a formula to identify any text in Column B which also appears in Column A. Then output ONLY the matching text(s) to a new Column C.

Does that make sense?? Any help would be very much appreciated.
Thanks in advance.

 

[Akashwani]

Hi,

Welcome to MrExcel, the best Excel site around.

Take a look here.....

http://www.youtube.com/user/ExcelIsFun#p/search/10/9bZQmC2VJKE

and you should be able to modify the formulas used to get this...

Excel Workbook
	A	B	C	D
1	Data1	Data2	Result	3
2	Brand One	Brand Two	Brand Two	*
3	Brand Two	Brand Two	Brand Four	*
4	Brand Three	Brand Two	Brand Six	*
5	Brand Four	Brand Six	*	*
6	Brand Five	Brand Six	*	*
7	Brand Six	Brand Six	*	*
8	Brand Seven	Brand Four	*	*
9	Brand Eight	Brand Four	*	*
10	Brand Nine	Brand Four	*	*
11	Brand Ten	Brand Two	*	*
Sheet4

Is that the result you would expect based on the sample data?
You will need to change the cell references to suit yours.
The formula in C2 needs entering with ctrl shift enter NOT enter.
You can then copy it down.

Mike has plenty of videos that may be of help to you, take a look....

http://www.youtube.com/user/ExcelIsFun#g/u

Good luck.

Ak

 

[rsw2011]

Hi there,

Thanks for replying!

What I actually meant was that

Column A = Brand one, Brand two, Brand three (TEXT STRING ALL ON ONE ROW)
Column B = Brand two (AGAIN ON ONE ROW)

So I want it to pick out the same text from within a whole bunch of words, is that not possible?!

Thanks!

 

[rsw2011]

Like this:

<style>*<!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {font-weight:700;} --> </style> <table style="border-collapse: collapse" border="0" cellpadding="0" cellspacing="0" width="769"> <col style="mso-width-source:userset;mso-width-alt:7350" width="201"> <col style="mso-width-source:userset;mso-width-alt:1682" width="46"> <col style="mso-width-source:userset;mso-width-alt:7936" width="217"> <col style="mso-width-source:userset;mso-width-alt:1974" width="54"> <col style="mso-width-source:userset;mso-width-alt:9179" width="251"> <tbody><tr height="13"> <td class="xl24" height="13" width="201">Data 1</td> <td class="xl24" width="46"></td> <td class="xl24" width="217">Data 2</td> <td class="xl24" width="54"></td> <td class="xl24" width="251">RESULT</td> </tr> <tr height="13"> <td height="13">Budweiser, Bud Light, Bud Ice</td> <td></td> <td>Budweiser, Corona</td> <td></td> <td>Budweiser</td> </tr> <tr height="13"> <td height="13">Budweiser, Bud Light, Bud Ice</td> <td></td> <td>Corona</td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">Budweiser, Bud Light, Bud Ice</td> <td></td> <td></td> <td></td> <td></td> </tr> <tr height="13"> <td height="13">Budweiser, Bud Light, Bud Ice</td> <td></td> <td>Bud Ice</td> <td></td> <td>Bud Ice</td> </tr> <tr height="13"> <td height="13">Budweiser, Bud Light, Bud Ice</td> <td></td> <td>Budweiser, Bud Light, Bud Ice</td> <td></td> <td>Budweiser, Bud Light, Bud Ice</td> </tr> </tbody></table>

Do you see what I mean? So it picks out ONLY the items in Data 2 that also appear in Data 1 and outputs ONLY those values to the result column.

Hope that makes sense!!

Thanks

 

[Akashwani]

Hi,

Sorry, I have no idea how to create a formula to do that.

You may now want to start a new thread stating that you want to extract matching text within a text string from within another text string.

Post the sample data that you have put on here and make it clear if you require a VBA or formula solution, VBA maybe the best way to go.

Sorry that I couldn't be of more help.

Good luck.

Ak

 

[rsw2011]

No worries, thank you for replying anyway!