Conditional formatting

[RAJESH1960]

Hi, I have already asked this question before and was answered by etaf, but I have one more correction to be made. So I am posting again .
I have figures in column F. I want to color the whole row if the value is unique, i.e., if it is not repeated. If the difference is less than 1 then too it should color the row. The second condition is that the values / text in column B should be the same.

Book4
	B	C	D	E	F
1	A				87091.00
2	A				87091.20
3	B				7040.00
4	C				114534.00
5	C				114534.40
6	D				275940.00
7	D				275940.22
8	E				13650.00
9	E				13650.50
10	F				33264.00
11	F				33264.00
12	G				34020.00
13	H				39060.00
14	H				39060.67
15	I				68250.00
16	I				68250.00
17	Z				68250.00
18	J				929.00
19	J				929.25
20	K				1147.00
21	K				1147.00
22	L				1549.00
23	L				1549.44
24	M				1549.00
25	M				1549.00
26	N				1859.00
27	N				1859.00
28	O				2514.00
29	O				2514.00
30	P				2514.00
31	P				2514.14
32	Q				4026.00
33	Q				4026.00
34	R				4956.00
35	R				4956.00
Sheet1

 

[RAJESH1960]

Hi, I have already asked this question before and was answered by etaf, but I have one more correction to be made. So I am posting again .
I have figures in column F. I want to color the whole row if the value is unique, i.e., if it is not repeated. If the difference is less than 1 then too it should color the row. The second condition is that the values / text in column B should be the same.

Book4
	B	C	D	E	F
1	A				87091.00
2	A				87091.20
3	B				7040.00
4	C				114534.00
5	C				114534.40
6	D				275940.00
7	D				275940.22
8	E				13650.00
9	E				13650.50
10	F				33264.00
11	F				33264.00
12	G				34020.00
13	H				39060.00
14	H				39060.67
15	I				68250.00
16	I				68250.00
17	Z				68250.00
18	J				929.00
19	J				929.25
20	K				1147.00
21	K				1147.00
22	L				1549.00
23	L				1549.44
24	M				1549.00
25	M				1549.00
26	N				1859.00
27	N				1859.00
28	O				2514.00
29	O				2514.00
30	P				2514.00
31	P				2514.14
32	Q				4026.00
33	Q				4026.00
34	R				4956.00
35	R				4956.00
Sheet1

Sorry, If the difference is less than 1 in column F and the value in the correspoding row are the same it should not color the row.

 

[etaf]

would you give some examples coloured in on your sheet

if you had say
1
2
3
4
6



Then 3 is 1 less than 4 , so the 4's and 3's would NOT highlight
BUT the 2 is 1 less then 3 and would NOT highlight
Then the 1 is 1 less then 2 and would NOThighlight

so in my example only 6 would be highlighted as nothing is 1 less then 6 and its on its own

 

[RAJESH1960]

would you give some examples coloured in on your sheet

if you had say
1
2
3
4
6



Then 3 is 1 less than 4 , so the 4's and 3's would NOT highlight
BUT the 2 is 1 less then 3 and would NOT highlight
Then the 1 is 1 less then 2 and would NOThighlight

so in my example only 6 would be highlighted as nothing is 1 less then 6 and its on its own

This is a reconciliation sheet. I have name in column B and the values in column F. I have colored the values of column F that do not match with the text in column B. Values in column F can be the same but the common factor to match is in column B.

Book4
	A	B	C	D	E	F
1		January				7091.00
2		January				7091.20
3		February				7091.20
4		March				4534.00
5		March				4534.40
6		April				5940.00
7		April				5940.22
8		May				5940.00
9		June				1365.00
10		July				1000.00
11		July				1000.15
12		August				1000.00
13		September				1000.15
14		September				1000.00
15		October				8500.00
16		October				8500.00
17		November				8500.00
18		December				929.00
19		December				929.25
20		Sunday				1147.00
21		Sunday				1147.12
22		Tuesday				1147.00
23		Tuesday				1147.14
24		Thursday				1549.00
25		Thursday				1549.00
26		Saturday				1859.00
27		Saturday				1859.00
28		Friday				2514.00
29		Friday				2514.00
30		Saturday				2514.00
format rows amount 2 conditions

 

[etaf]

so the examples make sense because of the last thread you had - those days are unique , ignoring he numbers

BUT Saturday, has a few entries and is highlighted , because its the only one with Saturday and 2514

BUT January is not because the numbers are within 1

Really trying to follow - maybe say why things are NOT highlighted

 

[RAJESH1960]

so the examples make sense because of the last thread you had - those days are unique , ignoring he numbers

BUT Saturday, has a few entries and is highlighted , because its the only one with Saturday and 2514

As there are no static values in the sheet except column B, it is getting difficult to match. Even in column B the values may occur repeatedly, but the amounts may be different. There are more than 4000 rows to match. A formula could help to solve it in seconds. In the above image, February, June, August, November and Saturday are a no match so I have highlighted them manually.

 

[etaf]

yes , i'm following , so saturday has more then one entry - BUT its the number that makes this highlight
if in your list
JANUARY - had in F 100
and the next
JANUARY - had in F 200
would they both be highlighted

Saturday has 3 entries - BUT 26&27 row has the same number and Saturday at row 30 is unique because of the number

 

[RAJESH1960]

yes , i'm following , so saturday has more then one entry - BUT its the number that makes this highlight
if in your list
JANUARY - had in F 100
and the next
JANUARY - had in F 200
would they both be highlighted

Saturday has 3 entries - BUT 26&27 row has the same number and Saturday at row 30 is unique because of the number

January the difference in amount is less than 1, 7091.00 & 7091.20 so it is not to be colored. If it was 7091 & 7092 then it will have to be colored as no match. Will a round up formula in the condition help.?

 

[etaf]

Ok, so i think i have something
BUT why is MAY not highlighted ?

I have used a tolarnace on the number of +- 0.5
BUT we can change that

so what if

7090.1 & 7091 & 7091.9
All within 1

i have used this
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)
and so testing if 0.5 difference

BUT it did highlight MAY

Book6
	A	B	C	D	E	F	G	H	I
1		January				7091			1
2		January				7091.2			1
3		February				7091.2		Y	1
4		March				4534			1
5		March				4534.4			1
6		April				5940			1
7		April				5940.22			1
8		May				5940			1
9		June				1365		Y	1
10		July				1000			1
11		July				1000.15			1
12		August				1000		Y	1
13		September				1000.15			1
14		September				1000			1
15		October				8500			2
16		October				8500			2
17		November				8500		Y	1
18		December				929			1
19		December				929.25			1
20		Sunday				1147			1
21		Sunday				1147.12			1
22		Tuesday				1147			1
23		Tuesday				1147.14			1
24		Thursday				1549			2
25		Thursday				1549			2
26		Saturday				1859			2
27		Saturday				1859			2
28		Friday				2514			2
29		Friday				2514			2
30		Saturday				2514		Y
Sheet1

Cell Formulas
Range	Formula
I1:I29	I1	=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,$F1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:F30	Expression	=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1	text	NO

 

[RAJESH1960]

Ok, so i think i have something
BUT why is MAY not highlighted ?

I have used a tolarnace on the number of +- 0.5
BUT we can change that

so what if

7090.1 & 7091 & 7091.9
All within 1

i have used this
=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)
and so testing if 0.5 difference

BUT it did highlight MAY

Book6
	A	B	C	D	E	F	G	H	I
1		January				7091			1
2		January				7091.2			1
3		February				7091.2		Y	1
4		March				4534			1
5		March				4534.4			1
6		April				5940			1
7		April				5940.22			1
8		May				5940			1
9		June				1365		Y	1
10		July				1000			1
11		July				1000.15			1
12		August				1000		Y	1
13		September				1000.15			1
14		September				1000			1
15		October				8500			2
16		October				8500			2
17		November				8500		Y	1
18		December				929			1
19		December				929.25			1
20		Sunday				1147			1
21		Sunday				1147.12			1
22		Tuesday				1147			1
23		Tuesday				1147.14			1
24		Thursday				1549			2
25		Thursday				1549			2
26		Saturday				1859			2
27		Saturday				1859			2
28		Friday				2514			2
29		Friday				2514			2
30		Saturday				2514		Y
Sheet1

Cell Formulas
Range	Formula
I1:I29	I1	=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,$F1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:F30	Expression	=COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,"<"&$F1+0.5,$F$1:$F$100,">"&$F1-0.5)=1	text	NO

etaf. It's Perfect. I forgot to color May. My mistake. Thanks once again etaf.