Compare two ranges, highlight cell with text in one range missing from other

[dkjonesau]

Hi all,

I've been trying to find a formula that I can use in a conditional format to solve this problem. I've searched quite a bit and still haven't found a solution.

In the table below Test 5 appears in the larger range B1:K3 but in the smaller range in column A (A4:A8) it's missing.

	A	B	C	D	E	F	G	H	I	J	K
1		Test 1	Test 1	Test 2	Test 2	Test 3	Test 3	Test 4	Test 4	Test 5	Test 5
2		Test 1	Test 1	Test 3	Test 1	Test 3	Test 3	Test 4	Test 4	Test 5	Test 5
3		Test 1	Test 1	Test 3	Test 5	Test 3	Test 3	Test 6	Test 4	Test 5	Test 5
4	Test 1
5	Test 2
6	Test 3
7	Test 4
8	Test 6

Is there a formula I can use to apply conditional formatting to range B1:K3 to highlight the cell containing Test 5?

In the real example the cells all contain text strings, not numbers.

Alternatively, is there a way to create the list in the smaller range using something like UNIQUE from the larger range? UNIQUE only appears to work on columns, not ranges across several columns.

Thanks,

Dave

 

[Cubist]

How about

=COUNTIF($A$4:$A$8,B1)<1

 

[dkjonesau]

How about

=COUNTIF($A$4:$A$8,B1)<1

Hi Cubist

I wanted to compare the entire range to the entire range if possible. Not have to setup 32 individual conditional format conditions and have to add more each time the bottom list grows.

Wouldn’t your example only compare B1 to the list? Other than the example here my real table is 70 columns wide, 26 high and the comparator list has 32 entries.

DJ

 

[Cubist]

Hi Cubist

I wanted to compare the entire range to the entire range if possible. Not have to setup 32 individual conditional format conditions and have to add more each time the bottom list grows.

Wouldn’t your example only compare B1 to the list? Other than the example here my real table is 70 columns wide, 26 high and the comparator list has 32 entries.

DJ

You would enter that formula in the conditional formatting for cell B1, then Ctrl + C to copy, and then highlight the rest of the cells. Ctrl + Alt + V - > All merging conditional formats.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Below is @Cubist's suggestion showing that you only need 1 rule. I have modified slightly as checking that the count is <1 seems a bit funny to me since the only count possible <1 is zero.
(I have altered the sample data slightly to show that still only 1 rule is required to highlight any value not appearing in the smaller list)

You would enter that formula in the conditional formatting for cell B1, then Ctrl + C to copy, and then highlight the rest of the cells. Ctrl + Alt + V - > All merging conditional formats.

I would normally do the implementation differently. I would select B1:K3 first and then apply the CF rule with Excel automatically adjusting the formula for the other selected cells.

24 03 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Test 1	Test 1	Test 2	Test 2	Test 3	Test 3	Test 4	Test 4	Test 5	Test 5
2		Test 1	Test 1	Test 3	Test 1	Test 3	abcd	Test 4	Test 4	Test 5	Test 5
3		Test 1	Test 1	Test 3	Test 5	Test 3	Test 3	Test 6	Test 4	Test 5	Test 5
4	Test 1
5	Test 2
6	Test 3
7	Test 4
8	Test 6
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:K3	Expression	=COUNTIF($A$4:$A$8,B1)=0	text	NO

 

[dkjonesau]

Thanks very much. Didn’t realize CF extrapolated like that. DJ

 

[Peter_SSs]

Thanks very much. Didn’t realize CF extrapolated like that. DJ

Glad you got it working.

.. but what about updating you version details as requested?