Excel formula if two cells have same word

[shacol03]

Hi,

Please help me, how to make a formula if two cells have multiple words and if they have same word will become true.
Example.

I want in Column C will "True" if Column A & B have same word.

A	B	C
Fc Innsbruck Union	Fc Stated Innsbruck	True
Fc Hoechst	Blau Hoechst Feldkirch	True
Al Hidd	Qalali	False
Dinamo Minsk	Minsk Mozyr	True

<tbody>
</tbody>

Thank you & Regards,
Roy

 

[Eric W]

Welcome to the forum.

This kind of thing is never pretty using formulas. It's much easier to maintain with a VBA user-defined function. However, the following formula does seem to work:

	A	B	C
1	Fc Innsbruck Union	Fc Stated Innsbruck	TRUE
2	Fc Hoechst	Blau Hoechst Feldkirch	TRUE
3	Al Hidd	Qalali	FALSE
4	Dinamo Minsk	Minsk Mozyr	TRUE
5	The Balloon Place	100 Ball Court	FALSE
6	100 Ball Court	The Balloon Place	FALSE
7	ABC Jack Street	Blackjack way	FALSE
8	Blackjack way	ABC Jack Street	FALSE
9	This is a very long line to test it	I don't know if every word here works very well	TRUE
10	This is a long line to test it	I don't know if every word here works very well	FALSE
11			TRUE

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula C1 {=ISNUMBER(AGGREGATE(15,6,SEARCH(MID(" "&A1&" ",IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1)),NA()),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" "),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Note 1: AGGREGATE was added in 2010, older versions of Excel can't handle this formula.

Note 2: When pasting your sample data, the spaces came across as character code 160. I don't know if that's how the data really is, or if it's just an artifact of the copy/pasting process. If the data actually has code 160 instead of spaces, change every instance of " " to CHAR(160) in the formula. Or use Replace All.

 

[shacol03]

Welcome to the forum.

This kind of thing is never pretty using formulas. It's much easier to maintain with a VBA user-defined function. However, the following formula does seem to work:

	A	B	C
1	Fc Innsbruck Union	Fc Stated Innsbruck	TRUE
2	Fc Hoechst	Blau Hoechst Feldkirch	TRUE
3	Al Hidd	Qalali	FALSE
4	Dinamo Minsk	Minsk Mozyr	TRUE
5	The Balloon Place	100 Ball Court	FALSE
6	100 Ball Court	The Balloon Place	FALSE
7	ABC Jack Street	Blackjack way	FALSE
8	Blackjack way	ABC Jack Street	FALSE
9	This is a very long line to test it	I don't know if every word here works very well	TRUE
10	This is a long line to test it	I don't know if every word here works very well	FALSE
11			TRUE

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula C1 {=ISNUMBER(AGGREGATE(15,6,SEARCH(MID(" "&A1&" ",IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1)),NA()),FIND(" "," "&A1&" ",ROW(INDIRECT("2:"&LEN(A1)+2)))-ROW(INDIRECT("1:"&LEN(A1)+1))+1)," "&B1&" "),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Note 1: AGGREGATE was added in 2010, older versions of Excel can't handle this formula.

Note 2: When pasting your sample data, the spaces came across as character code 160. I don't know if that's how the data really is, or if it's just an artifact of the copy/pasting process. If the data actually has code 160 instead of spaces, change every instance of " " to CHAR(160) in the formula. Or use Replace All.

Hi Sir Eric,

Thank you for the reply.

I'm sorry I have another problem. I want to apply in column (example below)

I want to put True in Column B, If Column A have same word in Column E within the column.

	A	B	E
1	Fc Innsbruck Union	True	Blau Hoechst Feldkirch
2	Fc Hoechst	True	Fc Stated Innsbruck
3	Al Hidd	False	Minsk Mozyr
4	Dinamo Minsk	True	Qalali
5	The Balloon Place	True	The Balloon Place
6	100 Ball Court	True	Blackjack Ace
7	ABC Jack Street	False	Court B

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

	A	B	C
1	Fc Innsbruck Union	Fc Stated Innsbruck	TRUE
2	Fc Hoechst	Blau Hoechst Feldkirch	TRUE
3	Al Hidd	Qalali	FALSE
4	Dinamo Minsk	Minsk Mozyr	TRUE
5	The Balloon Place	100 Ball Court	FALSE
6	100 Ball Court	The Balloon Place	FALSE
7	ABC Jack Street	Blackjack way	FALSE
8	Blackjack way	ABC Jack Street	FALSE
9	This is a very long line to test it	I don't know if every word here works very well	TRUE
10	This is a long line to test it	I don't know if every word here works very well	FALSE
11			TRUE

<tbody>
</tbody>

</body>
Thanks & Regards

HUHUHUHU -_-

 

[Eric W]

Just put the formula in B1 instead of C1, and change the B1 near the end to a C1.