Comparing 2 columns on 2 different excel sheets

Question

I need excel to compare 2 columns that are in different excel documents (Column A & Column B) I need excel to highlight a cell in Column A if the value contained in that cell is within a cell in Column B

Examples where excel should highlight a cell in Column A: Cell in Column A: "Hello" Cell in Column B: "Hello, My name is Bob"

Cell in Column A: "Hello" Cell in Column B: "Why,hello, My name is Bob"

How do I do this and is there any way to do this without writing my own function?

UPDATE: as long as it contains "Hello" so ("Helloahha", "Hello bob", "Hello8", "aiahHellooo", etc.?) should cause highlights

UPDATE:I tried using 'SEARCH($A1, 'Different_Excel_sheet'!$A1)', but it seems like excel didn't like that.

UPDATE: I fell like i needed to ad a bit more clarification to what i'm doing so here we go!

Col A | Column B
1234 | 9514, 6548, 3181, 9516
2356 | 1458, 1234, 1594
4568 | 9874
1594 | 9845, 9841
1563 | 4568

• The first cell in Column A (the one containing "1234") should be highlighted because "1234" is in column B (look at the second row)
• The third cell in Column A (the one containing "4568") should be highlighted because "4568" is in column B (look at the fifth row)

Answer 1

I don't believe conditional formatting will allow a reference to an external workbook. You can add a column to your first workbook that references the second. For example, you could create a cell in workbook A with the formula "=ISNUMBER(SEARCH(A1,[Book2]Sheet1!$a$1))", where A1 is the cell in the local workbook you want to compare with cell A1 in the the other workbook ("Book2" in this example). The example formula tests whether the text in cell A1 is contained within the string in Book2's A1 cell. It returns true or false.

You can then do the conditional formatting on the local cell A1. Select Conditional Formatting -> New Rule -> Use a formula to determine cells to format. Important: make sure you do not use =$C$3 instead of =C3 for the cell reference. Here's the screenshot. [Edit, updated picture to include cols and row heading]. You can then apply the formatting to every cell in the column.

Here are the two workbooks together.

Answer 2

You could use conditional formatting. I cannot provide a screenshot in english, however this is what you need to do:

1. Select all cells in column A
2. In the conditional formatting window enter this formula: =IF(COUNTIF(B:B;" * "&A3&" * ")>0;1)

B:B is the column in which you are searching the texts in column A. If column B is on another sheet, mention the name of the sheet in front of "B:B" (e.g. Sheet2!B:B) Make sure to use relative addresses like "A3" instead of "$A$3"

3. Decide how to style highlighted cells