Highlighting cell contents, based on a range

[wherethegoalsat]

Hi

I am trying to work out how to create a formula/conditional formatting for duplication. The conditions are as follows. (from the image)

1. Grey box is know as 'contents', Yellow box is 'New Lot'.
2. So IF the cell contents, in the yellow box matches ANY of the cell contents in the grey box, then BOTH of the cells would highlight (so red text for example).
3. If there are 2 matching cells in the grey box, then it WONT highlight the cells, as its only concerned with a match in the yellow box

In the example in the picture, i would expect to see
* fridge highlighted in cells A4 & A12
* saw highlighted in cells C5 & A14
* bed in cells C6 & A7 to NOT be highlighted

The thinking long term, is that the yellow 'new lot' section will constantly change and i can see whats a duplicate of the contents (grey section.).

Hope this made sense... not sure if its achievable.

 

[Snakehips]

@wherethegoalsat Welcome.

Does the below help?

Book1
	A	B	C
1	House	Garage	Shed
2
3	Cooker	Bench	lawnmower
4	Fridge	Bike	Bike
5	Shelf	TV	Saw
6	Computer		Bed
7	Bed
8
9
10	New Lot
11	bed
12	Fridge
13	Light
14	saw
15	Bowl
Sheet12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:A15	Expression	=AND(A11>"",COUNTIF($A$2:$C$7,A11))	text	NO
A2:C7	Expression	=AND(A2>"",COUNTIF($A$10:$A$15,A2)=1)	text	NO

 

[wherethegoalsat]

that would work GREAT

right... need to find out how to apply that conditional formatting now on the web version

 

[wherethegoalsat]

ok, so i have modified the formula and applied it to the real spreadsheet... but im getting errors... some text night highlighting (numbers) and some blank cells highlighting.

can you tell me whats wrong with what i have done?

 

[Snakehips]

It will help if you can use the XL2BB link below. Download that add-in and use it to post a mini-sheet of the section of your sheet as per your image.
Ensure that you tick in the XLBB sub menu to include conditional formatting.

 

[Snakehips]

Also, can you identify the cells of that dataset that you expect to be CF highlighted?

 

[felixstraube]

Didn't you want bed NOT to be highlighted?
Try this:

Book6.xlsx
	A	B	C
1	House	Garage	Shed
2
3	Cooker	Bench	lawnmower
4	Fridge	Bike	Bike
5	Shelf	TV	Saw
6	Computer		Bed
7	Bed	Light
8
9
10	New Lot
11	bed
12	Fridge
13	Light
14	saw
15	Bowl
16
17
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:A1000	Expression	=SUM(COUNTIF(A11, $A$3:$C$7))*(SUM(COUNTIF(A11, $A$3:$C$7))<2)	text	NO
A3:C7	Expression	=SUM(COUNTIF(A3, $A$11:$A$1000))*(SUM(COUNTIF(A3, $A$3:$C$7))<2)	text	NO

 

[felixstraube]

Actually the formula for the new lot CF can be simplified:

Book6.xlsx
	A	B	C
1	House	Garage	Shed
2
3	Cooker	Bench	lawnmower
4	Fridge	Bike	Bike
5	Shelf	TV	Saw
6	Computer		Bed
7	Bed	Light	Light
8
9
10	New Lot
11	bed
12	Fridge
13	Light
14	saw
15	Bowl
16
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:A1000	Expression	=SUM(COUNTIF(A11, $A$3:$C$7))=1	text	NO
A3:C7	Expression	=SUM(COUNTIF(A3, $A$11:$A$1000))*(SUM(COUNTIF(A3, $A$3:$C$7))<2)	text	NO

 

[wherethegoalsat]

Didn't you want bed NOT to be highlighted?
Try this:

Book6.xlsx
	A	B	C
1	House	Garage	Shed
2
3	Cooker	Bench	lawnmower
4	Fridge	Bike	Bike
5	Shelf	TV	Saw
6	Computer		Bed
7	Bed	Light
8
9
10	New Lot
11	bed
12	Fridge
13	Light
14	saw
15	Bowl
16
17
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:A1000	Expression	=SUM(COUNTIF(A11, $A$3:$C$7))*(SUM(COUNTIF(A11, $A$3:$C$7))<2)	text	NO
A3:C7	Expression	=SUM(COUNTIF(A3, $A$11:$A$1000))*(SUM(COUNTIF(A3, $A$3:$C$7))<2)	text	NO

i did want bed to NOT be highlighted yes... so that solution looks great, i have modified it to my master spreadsheet and again its failing.... i must be doing something wrong when i try and re-populate the cell details (see pic).... also i have been trying to install XL2BB, but its against company policy and wont allow it.

in the image you can see 'yansa' not highlighting.. and triple threat.

 

[felixstraube]

Try this

here is the working file to download:

CondiFormat.zip

Book6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		House	Garage	Shed
2
3
4		Cooker	Bench	lawnmower
5		Fridge	Bike	Bike
6		Shelf	TV	Saw
7		Computer		Bed
8		Bed	Light	Light
9										Yansa
10
11
12
13
14
15
16
17
18
19
20
21		bed
22		Fridge
23		Light
24		saw
25		Bowl
26		Yansa
27
28
29
30
Sheet4 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B21:B999	Expression	=SUM(COUNTIF(B21, $B$4:$K$18))=1	text	NO
B4:K17	Expression	=SUM(COUNTIF(B4, $B$21:$B$999))*(SUM(COUNTIF(B4, $B$4:$K$18))<2)	text	NO