find the top 3 and lowest number using the condition formatting

[mwvirk]

Hi,
I am trying to find the top 3 numbers in a cell range and then the lowest number but it seems it's not working to get the lowest number or sometimes it's changing the condition formatting for the full range. Like if the cell value is 0 then it will be considered as the lowest number and marked as BLACK but if somehow the cell value is blank then it's still marked as BLACK.
I would appreciate it if someone could help me here no matter if you want to change the color of the blank value to some other color.

Please also note that I will be copying the same condition formatting for the other 3000 records (each record has 50 entries as shown below screenshot (1 record of 50 entries)
All cells will have numeric values only.

Election 2024.xlsx
	L	M
2	10	0.00%
3	20	0.00%
4	30	0.01%
5	40	0.01%
6	50	0.01%
7	60	0.01%
8	70	0.02%
9	80	0.02%
10	90	0.02%
11	90	0.02%
12	90	0.02%
13	90	0.02%
14	90	0.02%
15	3	0.00%
16	1	0.00%
17	90	0.02%
18	0	0.00%
19	90	0.02%
20	90	0.02%
21	90	0.02%
22	90	0.02%
23	3	0.00%
24	6	0.00%
25	1	0.00%
26	90	0.02%
27	90	0.02%
28	90	0.02%
29	90	0.02%
30	90	0.02%
31	100	0.02%
32	200	0.05%
33	300	0.07%
34	400	0.09%
35	500	0.11%
36	111,000	25.39%
37	122,000	27.90%
38	133,000	30.42%
39	5	0.00%
40	1,000	0.23%
41	2,000	0.46%
42
43	4,000	0.91%
44	5,000	1.14%
45	6,000	1.37%
46
47	8,000	1.83%
48	9,000	2.06%
49	10,000	2.29%
50	11,000	2.52%
51	12,000	2.74%
Election 2022 (MNA)

Cell Formulas
Range	Formula
M2:M51	M2	=IF(L2="","",L2/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L51	Expression	=OR(L2=MIN(L$2:L$51))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,1))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,2))	text	NO
L2:L51	Expression	=OR(L2=MAX(L$2:L$51),L2=LARGE(L$2:L$51,3))	text	NO
M2:M51	Expression	=OR(M2=MIN(M$2:M$51))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,1))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,2))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,3))	text	NO

 

[Peter_SSs]

finding the top 3 issue are also resolved. so we can ignore it. thanks.

Out of interest in something we have put a bit of time into, would you care to share how you did that for the multiple ranges?

I also want to find the number that is the lowest but shouldn't be 0 (next higher number than 0)

Just clarifying: Do you actually mean shouldn't be 0 or do you mean shouldn't be blank?
In your earlier pictures some have blank cells highlighted and some have 0 cells highlighted so I'm not sure.
For example if the range contained only the values below, would you want the 3 or the 0 highlighted?

mwvirk.xlsm
	L
2	6
3
4	3
5	0
6	4
Sheet2

 

[mwvirk]

Election 2024 - Copy.xlsx
	L	M	N
2	10	0.00%	42
3	20	0.00%	41
4	30	0.00%	40
5	40	0.00%	39
6	50	0.00%	38
7	60	0.00%	37
8	70	0.00%	36
9	80	0.00%	35
10	144,000	2.69%	9
11	144,000	2.69%	9
12	90	0.00%	25
13	90	0.00%	25
14	90	0.00%	25
15	3	0.00%	44
16	88	0.00%	34
17	90	0.00%	25
18	99	0.00%	22
19	99	0.00%	22
20	90	0.00%	25
21	90	0.00%	25
22	90	0.00%	25
23	1	0.00%	46
24	6	0.00%	43
25	2	0.00%	45
26	0	0.00%	47
27	99	0.00%	22
28			47
29	90	0.00%	25
30	90	0.00%	25
31	333,333	6.22%	7
32	555,555	10.37%	5
33	444,444	8.30%	6
34	333,333	6.22%	7
35	222	0.00%	21
36	666,666	12.44%	4
37	777,777	14.52%	3
38	888,888	16.59%	2
39	999,999	18.66%	1
40	1,000	0.02%	20
41	2,000	0.04%	19
42			47
43	4,000	0.07%	18
44	5,000	0.09%	17
45	6,000	0.11%	16
46			47
47	8,000	0.15%	15
48	9,000	0.17%	14
49	10,000	0.19%	13
50	11,000	0.21%	12
51	12,000	0.22%	11
52	10	0.00%	42
53	20	0.00%	41
54	30	0.00%	40
55	40	0.00%	39
56	50	0.00%	38
57	60	0.00%	37
58	70	0.00%	36
59	80	0.00%	35
60	144,000	2.69%	9
61	144,000	2.69%	9
62	90	0.00%	25
63	90	0.00%	25
64	90	0.00%	25
65	3	0.00%	44
66	88	0.00%	34
67	90	0.00%	25
68	99	0.00%	22
69	99	0.00%	22
70	90	0.00%	25
71	90	0.00%	25
72	90	0.00%	25
73	1	0.00%	46
74	6	0.00%	43
75	2	0.00%	45
76	0	0.00%	47
77	99	0.00%	22
78			47
79	90	0.00%	25
80	90	0.00%	25
81	333,333	6.22%	7
82	555,555	10.37%	5
83	444,444	8.30%	6
84	333,333	6.22%	7
85	222	0.00%	21
86	666,666	12.44%	4
87	777,777	14.52%	3
88	888,888	16.59%	2
89	999,999	18.66%	1
90	1,000	0.02%	20
91	2,000	0.04%	19
92			47
93	4,000	0.07%	18
94	5,000	0.09%	17
95	6,000	0.11%	16
96			47
97	8,000	0.15%	15
98	9,000	0.17%	14
99	10,000	0.19%	13
100	11,000	0.21%	12
101	12,000	0.22%	11
102	10	0.00%	42
103	20	0.00%	41
104	30	0.00%	40
105	40	0.00%	39
106	50	0.00%	38
107	60	0.00%	37
108	70	0.00%	36
109	80	0.00%	35
110	144,000	2.53%	10
111	144,000	2.53%	10
112	90	0.00%	25
113	90	0.00%	25
114	90	0.00%	25
115	3	0.00%	44
116	88	0.00%	34
117	90	0.00%	25
118	99	0.00%	22
119	99	0.00%	22
120	90	0.00%	25
121	90	0.00%	25
122	90	0.00%	25
123	1	0.00%	46
124	6	0.00%	43
125	2	0.00%	45
126	0	0.00%	47
127	99	0.00%	22
128			47
129	90	0.00%	25
130	90	0.00%	25
131	333,333	5.86%	8
132	555,555	9.76%	4
133	444,444	7.81%	7
134	333,333	5.86%	8
135	555,555	9.76%	4
136	666,666	11.71%	3
137	999,999	17.57%	1
138	888,888	15.62%	2
139	555,555	9.76%	4
140	1,000	0.02%	21
141	2,000	0.04%	20
142			47
143	4,000	0.07%	19
144	5,000	0.09%	18
145	6,000	0.11%	17
146			47
147	8,000	0.14%	16
148	9,000	0.16%	15
149	10,000	0.18%	14
150	11,000	0.19%	13
151	12,000	0.21%	12
Election 2022 (MNA)

Cell Formulas
Range	Formula
M2:M51	M2	=IF(L2="","",L2/$H$2)
N2:N151	N2	=RANK($L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51))
M52:M101	M52	=IF(L52="","",L52/$H$52)
M102:M151	M102	=IF(L102="","",L102/$H$102)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L102:L151	Cell	contains a blank value	text	NO
L52:L101	Cell	contains a blank value	text	NO
J2:N51	Cell	contains a blank value	text	NO
M52:M13601	Expression	=OR(M52=MIN(M$2:M$51))	text	NO
M52:M13601	Expression	=OR(M52=MAX(M$2:M$51),M52=LARGE(M$2:M$51,1))	text	NO
M52:M13601	Expression	=OR(M52=MAX(M$2:M$51),M52=LARGE(M$2:M$51,2))	text	NO
M52:M13601	Expression	=OR(M52=MAX(M$2:M$51),M52=LARGE(M$2:M$51,3))	text	NO
M2:M51	Expression	=AND($M2<>"",$M2=MIN($M$2:$M$51))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,1))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,2))	text	NO
M2:M51	Expression	=OR(M2=MAX(M$2:M$51),M2=LARGE(M$2:M$51,3))	text	NO
L2:L151	Expression	=AND($L2<>"",$L2=MIN($L$2:$L$51))	text	NO
L2:L151	Expression	=$N2=1	text	NO
L2:L151	Expression	=$N2=2	text	NO
L2:L151	Expression	=$N2=3	text	NO

 

[mwvirk]

Out of interest in something we have put a bit of time into, would you care to share how you did that for the multiple ranges?



Just clarifying: Do you actually mean shouldn't be 0 or do you mean shouldn't be blank?
In your earlier pictures some have blank cells highlighted and some have 0 cells highlighted so I'm not sure.
For example if the range contained only the values below, would you want the 3 or the 0 highlighted?

mwvirk.xlsm
	L
2	6
3
4	3
5	0
6	4
Sheet2

results shared in the above post for the top 3

I want the lowest number. but it should not be 0
e.g. if cell values are
11
12
13
14
15
16
0
0
BLANK
19
20

then 11 must be considered the lowest number (pending)
20 19 16 are top 3 (this is resolved)

 

[mwvirk]

*should not be 0

 

[Peter_SSs]

results shared in the above post for the top 3

I'm afraid that I am getting lost. I thought that post #8 meant that you had solved the top 3 issue but from what appears in post #12 it seem to me that the only part that might work over your multiple records is the suggestion that I subsequently made in post #9.

Unless I am mis-understanding what you are trying to do, you have multiple CF rules that will not do what you want. For example the yellow highlighted rules below are applied to rows 52:13601 but are based on the values in rows 2:51 only so they will not work correctly in rows below 51.
The green highlighted rules only apply to the first record so they also will not be any use for other records.

Why do you have so many rules? You have 15 rules listed. Do you have 15 different formatting that you want applied? Should some of the rules be deleted?

Also, in earlier posts you wanted the top 3 with 3 different colours but in your last mini-sheet all 3 are black.

Until any mis-understanding outlined above is resolved, this is what I was going to suggest for 3 different colours for the top 3 and another colour for lowest above zero. 4 CF rules only.
I have assumed that there would not be any negative numbers in column L.

mwvirk.xlsm
	L	M	N	O	P
1
2	50			5	5
3	90			1	9
4	80			2	8
5	70			4	6
6	10			8	2
7	18			7	3
8	42			6	4
9	75			3	7
10				10	-1
11	0			10	-1
12	6			9	1
13				10	-1
14				10	-1
48	0			10	-1
49				10	-1
50				10	-1
51				10	-1
52	30			6	4
53	8			9	1
54	16			8	2
55	70			3	7
56	62			4	6
57	44			5	5
58	28			7	3
59				10	-3
83				10	-3
84				10	-3
85	86			2	8
86				10	-3
87	0			10	-3
88	0			10	-3
89	0			10	-3
90	0			10	-3
91				10	-3
92	92			1	9
93				10	-3
94				10	-3
99				10	-3
100				10	-3
101				10	-3
Sheet1

Cell Formulas
Range	Formula
O2:O14,O48:O59,O83:O94,O99:O101	O2	=RANK($L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51))
P2:P14,P48:P59,P83:P94,P99:P101	P2	=RANK(L2,INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51),1)-COUNTIF(INDEX($L:$L,FLOOR(ROW()-2,50)+2):INDEX($L:$L,FLOOR(ROW()-2,50)+51),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M101	Expression	=$P2=1	text	NO
L2:M101	Expression	=$O2=3	text	NO
L2:M101	Expression	=$O2=2	text	NO
L2:M101	Expression	=$O2=1	text	NO

 

[mwvirk]

Thank you so much, Peter.

appreciate it if you could also help resolve the J & K cells so I can get rid of multiple CFs

Election 2024.xlsx
	J	K	L	M
2	Candidate Name 0001	Party Name A	10	0.00%
3	Candidate Name 0002	Party Name B	20	0.00%
4	Candidate Name 0003	Party Name C	30	0.00%
5	Candidate Name 0004	Party Name D	40	0.00%
6	Candidate Name 0005	Party Name E	50	0.00%
7	Candidate Name 0006	Party Name F	0	0.00%
8	Candidate Name 0007	Party Name G	70	0.00%
9	Candidate Name 0008	Party Name H	80	0.00%
10	Candidate Name 0009	Party Name I	144,000	3.65%
11	Candidate Name 0010	Party Name J	144,000	3.65%
12	Candidate Name 0011	Party Name K	90	0.00%
13	Candidate Name 0012	Party Name L	90	0.00%
14	Candidate Name 0013		90	0.00%
15	Candidate Name 0014	Party Name N	3	0.00%
16	Candidate Name 0015	Party Name O	88
17	Candidate Name 0016	Party Name P	90	0.00%
18	Candidate Name 0017	Party Name Q	99	0.00%
19	Candidate Name 0018	Party Name R	0	0.00%
20		Party Name S	90	0.00%
21	Candidate Name 0020	Party Name T	90	0.00%
22	Candidate Name 0021	Party Name U
23	Candidate Name 0022	Party Name V	3	0.00%
24	Candidate Name 0023	Party Name W	6	0.00%
25	Candidate Name 0023	Party Name W	2	0.00%
26	Candidate Name 0023	Party Name W	0	0.00%
27	Candidate Name 0026	Party Name Z	99	0.00%
28	Candidate Name 0027	Party Name AA
29	Candidate Name 0028	Party Name AB	90	0.00%
30	Candidate Name 0029	Party Name AC	90	0.00%
31	Candidate Name 0030	Party Name AD	999,999	25.34%
32	Candidate Name 0031	Party Name AE	666,666	16.90%
33	Candidate Name 0032	Party Name AF	444,444	11.26%
34	Candidate Name 0033	Party Name AG	333,333	8.45%
35	Candidate Name 0034	Party Name AH	888,888	22.53%
36	Candidate Name 0035	Party Name AI	222	0.01%
37	Candidate Name 0036	Party Name AJ	122,000	3.09%
38	Candidate Name 0037	Party Name AK	133,000	3.37%
39	Candidate Name 0038	Party Name AL	5	0.00%
40	Candidate Name 0039	Party Name AM	1,000	0.03%
41	Candidate Name 0040	Party Name AN	2,000	0.05%
42	Candidate Name 0041	Party Name AO
43	Candidate Name 0042	Party Name AP	4,000	0.10%
44	Candidate Name 0043	Party Name AQ	5,000	0.13%
45	Candidate Name 0044	Party Name AR	6,000	0.15%
46	Candidate Name 0045	Party Name AS
47	Candidate Name 0046	Party Name AT	8,000	0.20%
48	Candidate Name 0047	Party Name AU	9,000	0.23%
49	Candidate Name 0048	Party Name AV	10,000	0.25%
50	Candidate Name 0049	Party Name AW	11,000	0.28%
51	Candidate Name 0050	Party Name AX	12,000	0.30%
Election 2022 (MNA)

Cell Formulas
Range	Formula
M17:M51,M2:M15	M2	=IF(L2="","",L2/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M13601	Expression	=AND($L2<>"",$L2=MIN($L2:$M13601))	text	NO
L2:M13601	Expression	=$AB2=1	text	NO
L2:M13601	Expression	=$AA2=3	text	NO
L2:M13601	Expression	=$AA2=2	text	NO
L2:M13601	Expression	=$AA2=1	text	NO
J2:M13601	Cell	contains a blank value	text	NO
K2:K51	Cell Value	duplicates	text	NO
J2:J51	Cell Value	duplicates	text	NO

 

[mwvirk]

I have 272 records now in my sheep (reduce/split so the sheet can work faster)
in each record, I have 50 entries (candidates) and a party name
right now I am checking all 50 entries for 272 records and each record has a CF
the way you helped to find the topper/lower by adding a dummy column, if the similar can be done for J & K can I can remove lot of CFs

 

[mwvirk]

i am sharing the 1st 3 records to understand the requirement in better way:

Election 2024.xlsx
	J	K	L	M
2	Candidate Name 0001	Party Name A	10	0.00%
3	Candidate Name 0002	Party Name B	20	0.00%
4	Candidate Name 0003	Party Name C	30	0.00%
5	Candidate Name 0004	Party Name D	40	0.00%
6	Candidate Name 0005	Party Name E	50	0.00%
7	Candidate Name 0006	Party Name F	0	0.00%
8	Candidate Name 0007	Party Name G	70	0.00%
9	Candidate Name 0008	Party Name H	80	0.00%
10	Candidate Name 0009	Party Name I	144,000	3.65%
11	Candidate Name 0010	Party Name J	144,000	3.65%
12	Candidate Name 0011	Party Name K	90	0.00%
13	Candidate Name 0012	Party Name L	90	0.00%
14	Candidate Name 0013		90	0.00%
15	Candidate Name 0014	Party Name N	3	0.00%
16	Candidate Name 0015	Party Name O	88
17	Candidate Name 0016	Party Name P	90	0.00%
18	Candidate Name 0017	Party Name Q	99	0.00%
19	Candidate Name 0018	Party Name R	0	0.00%
20		Party Name S	90	0.00%
21	Candidate Name 0020	Party Name T	90	0.00%
22	Candidate Name 0021	Party Name U
23	Candidate Name 0022	Party Name V	3	0.00%
24	Candidate Name 0023	Party Name W	6	0.00%
25	Candidate Name 0023	Party Name W	2	0.00%
26	Candidate Name 0023	Party Name W	0	0.00%
27	Candidate Name 0026	Party Name Z	99	0.00%
28	Candidate Name 0027	Party Name AA
29	Candidate Name 0028	Party Name AB	90	0.00%
30	Candidate Name 0029	Party Name AC	90	0.00%
31	Candidate Name 0030	Party Name AD	999,999	25.34%
32	Candidate Name 0031	Party Name AE	666,666	16.90%
33	Candidate Name 0032	Party Name AF	444,444	11.26%
34	Candidate Name 0033	Party Name AG	333,333	8.45%
35	Candidate Name 0034	Party Name AH	888,888	22.53%
36	Candidate Name 0035	Party Name AI	222	0.01%
37	Candidate Name 0036	Party Name AJ	122,000	3.09%
38	Candidate Name 0037	Party Name AK	133,000	3.37%
39	Candidate Name 0038	Party Name AL	5	0.00%
40	Candidate Name 0039	Party Name AM	1,000	0.03%
41	Candidate Name 0040	Party Name AN	2,000	0.05%
42	Candidate Name 0041	Party Name AO
43	Candidate Name 0042	Party Name AP	4,000	0.10%
44	Candidate Name 0043	Party Name AQ	5,000	0.13%
45	Candidate Name 0044	Party Name AR	6,000	0.15%
46	Candidate Name 0045	Party Name AS
47	Candidate Name 0046	Party Name AT	8,000	0.20%
48	Candidate Name 0047	Party Name AU	9,000	0.23%
49	Candidate Name 0048	Party Name AV	10,000	0.25%
50	Candidate Name 0049	Party Name AW	11,000	0.28%
51	Candidate Name 0050	Party Name AX	12,000	0.30%
52	Candidate Name 0001	Party Name A	10	0.00%
53	Candidate Name 0002	Party Name B	20	0.00%
54	Candidate Name 0003	Party Name C	30	0.01%
55	Candidate Name 0004	Party Name D	40	0.01%
56	Candidate Name 0005	Party Name E	50	0.01%
57	Candidate Name 0006		60	0.01%
58	Candidate Name 0007	Party Name G	70	0.01%
59		Party Name H	80	0.02%
60	Candidate Name 0009	Party Name I	90	0.02%
61	Candidate Name 0010	Party Name J	90	0.02%
62	Candidate Name 0011	Party Name K
63	Candidate Name 0012	Party Name L	90	0.02%
64	Candidate Name 0013	Party Name M	90	0.02%
65	Candidate Name 0014	Party Name N	90	0.02%
66	Candidate Name 0015	Party Name O	1	0.00%
67	Candidate Name 0016	Party Name P	90	0.02%
68	Candidate Name 0017	Party Name Q	90
69	Candidate Name 0018	Party Name R	90	0.02%
70	Candidate Name 0019	Party Name S	90	0.02%
71	Candidate Name 0020	Party Name T	90	0.02%
72	Candidate Name 0021	Party Name U	90	0.02%
73	Candidate Name 0022	Party Name V	90	0.02%
74	Candidate Name 0023	Party Name W	90	0.02%
75	Candidate Name 0023	Party Name W	90	0.02%
76	Candidate Name 0025	Party Name Y	90	0.02%
77	Candidate Name 0026	Party Name Z	90	0.02%
78	Candidate Name 0027	Party Name AA	90	0.02%
79	Candidate Name 0028	Party Name AB	90	0.02%
80	Candidate Name 0029	Party Name AC	80,000	15.96%
81	Candidate Name 0030	Party Name AD	75,000	14.96%
82	Candidate Name 0031	Party Name AE	85,000	16.96%
83	Candidate Name 0032	Party Name AF	300	0.06%
84	Candidate Name 0033	Party Name AG	400	0.08%
85	Candidate Name 0034	Party Name AH	500	0.10%
86	Candidate Name 0035	Party Name AI	50,000	9.98%
87	Candidate Name 0036	Party Name AJ	60,000	11.97%
88	Candidate Name 0037	Party Name AK	70,000	13.97%
89	Candidate Name 0038	Party Name AL	5	0.00%
90	Candidate Name 0039	Party Name AM	1,000	0.20%
91	Candidate Name 0040	Party Name AN	2,000	0.40%
92	Candidate Name 0041	Party Name AO	3,000	0.60%
93	Candidate Name 0042	Party Name AP	4,000	0.80%
94	Candidate Name 0043	Party Name AQ	5,000	1.00%
95	Candidate Name 0044	Party Name AR	6,000	1.20%
96	Candidate Name 0045	Party Name AS	7,000	1.40%
97	Candidate Name 0046	Party Name AT	8,000	1.60%
98	Candidate Name 0047	Party Name AU	9,000	1.80%
99	Candidate Name 0048	Party Name AV	10,000	2.00%
100	Candidate Name 0049	Party Name AW	11,000	2.19%
101	Candidate Name 0050	Party Name AX	12,000	2.39%
102	Candidate Name 0001	Party Name A	10	0.00%
103	Candidate Name 0002	Party Name B	20	0.01%
104	Candidate Name 0003	Party Name C	30	0.01%
105	Candidate Name 0004	Party Name D	40	0.02%
106	Candidate Name 0005	Party Name E	50	0.02%
107	Candidate Name 0006	Party Name F	60	0.02%
108	Candidate Name 0007	Party Name G	70	0.03%
109	Candidate Name 0008	Party Name H	80	0.03%
110	Candidate Name 0009	Party Name I	90	0.03%
111	Candidate Name 0010	Party Name J	90	0.03%
112	Candidate Name 0011	Party Name K	90	0.03%
113	Candidate Name 0012	Party Name L	90	0.03%
114	Candidate Name 0013	Party Name M	90	0.03%
115	Candidate Name 0014	Party Name N	90	0.03%
116	Candidate Name 0015	Party Name O	1	0.00%
117	Candidate Name 0016	Party Name P	90	0.03%
118	Candidate Name 0017	Party Name Q	90	0.03%
119	Candidate Name 0018	Party Name R	90	0.03%
120	Candidate Name 0019	Party Name S	90	0.03%
121	Candidate Name 0020	Party Name T	90	0.03%
122	Candidate Name 0021	Party Name U	90	0.03%
123	Candidate Name 0022	Party Name V	90	0.03%
124	Candidate Name 0023	Party Name W	90	0.03%
125	Candidate Name 0023	Party Name W	90	0.03%
126	Candidate Name 0025	Party Name Y	90	0.03%
127	Candidate Name 0026	Party Name Z	90	0.03%
128	Candidate Name 0027	Party Name AA	90	0.03%
129	Candidate Name 0028	Party Name AB	90	0.03%
130	Candidate Name 0029	Party Name AC	90	0.03%
131	Candidate Name 0030	Party Name AD	100	0.04%
132	Candidate Name 0031	Party Name AE	200	0.08%
133	Candidate Name 0032	Party Name AF	300	0.11%
134	Candidate Name 0033	Party Name AG	400	0.15%
135	Candidate Name 0034	Party Name AH	500	0.19%
136	Candidate Name 0035	Party Name AI	50,000	19.11%
137	Candidate Name 0036	Party Name AJ	60,000	22.93%
138	Candidate Name 0037	Party Name AK	70,000	26.75%
139	Candidate Name 0038	Party Name AL	5	0.00%
140	Candidate Name 0039	Party Name AM	1,000	0.38%
141	Candidate Name 0040	Party Name AN	2,000	0.76%
142	Candidate Name 0041	Party Name AO	3,000	1.15%
143	Candidate Name 0042	Party Name AP	4,000	1.53%
144	Candidate Name 0043	Party Name AQ	5,000	1.91%
145	Candidate Name 0044	Party Name AR	6,000	2.29%
146	Candidate Name 0045	Party Name AS	7,000	2.68%
147	Candidate Name 0046	Party Name AT	8,000	3.06%
148	Candidate Name 0047	Party Name AU	9,000	3.44%
149	Candidate Name 0048	Party Name AV	10,000	3.82%
150	Candidate Name 0049	Party Name AW	11,000	4.20%
151	Candidate Name 0050	Party Name AX	12,000	4.59%
Election 2022 (MNA)

Cell Formulas
Range	Formula
M17:M51,M2:M15	M2	=IF(L2="","",L2/$H$2)
M69:M101,M52:M67	M52	=IF(L52="","",L52/$H$52)
M102:M151	M102	=IF(L102="","",L102/$H$102)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M13601	Expression	=AND($L2<>"",$L2=MIN($L2:$M13601))	text	NO
L2:M13601	Expression	=$AB2=1	text	NO
L2:M13601	Expression	=$AA2=3	text	NO
L2:M13601	Expression	=$AA2=2	text	NO
L2:M13601	Expression	=$AA2=1	text	NO
J2:M13601	Cell	contains a blank value	text	NO
K102:K151	Cell Value	duplicates	text	NO
J102:J151	Cell Value	duplicates	text	NO
K52:K101	Cell Value	duplicates	text	NO
J52:J101	Cell Value	duplicates	text	NO
K2:K51	Cell Value	duplicates	text	NO
J2:J51	Cell Value	duplicates	text	NO

 

[Peter_SSs]

Your requirement is not clear. I suggest that you

• Make a copy of that last worksheet shown
• Remove all Conditional Formatting from the sheet
• Manually colour the required cells that you are now talking about (J:K?) the colour you want (except use another colour instead of black so the cell values can still easily be seen)
• Post those first 3 records again with XL2BB
• Explain exactly how you determined to what cells to colour. Remember that I know nothing about your data or requirements other than what you tell & show in the forum.

 

[mwvirk]

Your requirement is not clear. I suggest that you

• Make a copy of that last worksheet shown
• Remove all Conditional Formatting from the sheet
• Manually colour the required cells that you are now talking about (J:K?) the colour you want (except use another colour instead of black so the cell values can still easily be seen)
• Post those first 3 records again with XL2BB
• Explain exactly how you determined to what cells to colour. Remember that I know nothing about your data or requirements other than what you tell & show in the forum.

as you know, I am distributing the points to different candidates (L:L) <<< This is for ref only
now in cell J, I have the candidate's name, and cell K is his party.
from J2:J51, I have the candidate's name as a 1st record
then J52:J101 then J102:J151
on the other hand, against all candidates, I have mentioned their party name in K2:K51 as record 1 then K52:K101 2nd record then K102:K151, and so on
I want to check if each record (2:51 52:101 102:151) has any duplicate names and a duplicate party name then highlight it
in addition to the above, if the party name is a duplicate then the candidate name must be highlighted, and if the candidate name is a duplicate the part name must be highlighted.

Election 2024 - Copy.xlsx
	J	K
2	Candidate Name 0001	Party Name A
3	Candidate Name 0002	Party Name B
4	Candidate Name 0003	Party Name C
5	Candidate Name 0004	Party Name D
6	Candidate Name 0005	Party Name E
7	Candidate Name 0006	Party Name F
8	Candidate Name 0007	Party Name G
9	Candidate Name 0008	Party Name H
10	Candidate Name 0009	Party Name I
11	Candidate Name 0010	Party Name J
12	Candidate Name 0011	Party Name K
13	Candidate Name 0012	Party Name L
14	Candidate Name 0013
15	Candidate Name 0014	Party Name N
16	Candidate Name 0015	Party Name O
17	Candidate Name 0016	Party Name P
18	Candidate Name 0017	Party Name Q
19	Candidate Name 0018	Party Name R
20		Party Name S
21	Candidate Name 0020	Party Name T
22	Candidate Name 0021	Party Name U
23	Candidate Name 0022	Party Name V
24	Candidate Name 0023	Party Name W
25	Candidate Name 0023	Party Name W
26	Candidate Name 0023	Party Name W
27	Candidate Name 0026	Party Name Z
28	Candidate Name 0027	Party Name AA
29	Candidate Name 0028	Party Name AB
30	Candidate Name 0029	Party Name AC
31	Candidate Name 0030	Party Name AD
32	Candidate Name 0031	Party Name AE
33	Candidate Name 0032	Party Name AF
34	Candidate Name 0033	Party Name AG
35	Candidate Name 0034	Party Name AH
36	Candidate Name 0035	Party Name AI
37	Candidate Name 0036	Party Name AJ
38	Candidate Name 0037	Party Name AK
39	Candidate Name 0038	Party Name AL
40	Candidate Name 0039	Party Name AM
41	Candidate Name 0040	Party Name AN
42	Candidate Name 0041	Party Name AO
43	Candidate Name 0042	Party Name AP
44	Candidate Name 0043	Party Name AQ
45	Candidate Name 0044	Party Name AR
46	Candidate Name 0045	Party Name AS
47	Candidate Name 0046	Party Name AT
48	Candidate Name 0047	Party Name AU
49	Candidate Name 0048	Party Name AV
50	Candidate Name 0049	Party Name AW
51	Candidate Name 0050	Party Name AX
52	Candidate Name 0001	Party Name A
53	Candidate Name 0002	Party Name B
54	Candidate Name 0003	Party Name C
55	Candidate Name 0004	Party Name D
56	Candidate Name 0005	Party Name E
57	Candidate Name 0006
58	Candidate Name 0007	Party Name G
59		Party Name H
60	Candidate Name 0009	Party Name I
61	Candidate Name 0010	Party Name J
62	Candidate Name 0011	Party Name K
63	Candidate Name 0012	Party Name L
64	Candidate Name 0013	Party Name M
65	Candidate Name 0014	Party Name N
66	Candidate Name 0015	Party Name O
67	Candidate Name 0016	Party Name P
68	Candidate Name 0017	Party Name Q
69	Candidate Name 0018	Party Name R
70	Candidate Name 0019	Party Name S
71	Candidate Name 0020	Party Name T
72	Candidate Name 0021	Party Name U
73	Candidate Name 0022	Party Name V
74	Candidate Name 0023	Party Name W
75	Candidate Name 0024	Party Name X
76	Candidate Name 0025	Party Name Y
77	Candidate Name 0026	Party Name Z
78	Candidate Name 0027	Party Name AA
79	Candidate Name 0028	Party Name AB
80	Candidate Name 0029	Party Name AC
81	Candidate Name 0030	Party Name AD
82	Candidate Name 0031	Party Name AE
83	Candidate Name 0032	Party Name AF
84	Candidate Name 0033	Party Name AG
85	Candidate Name 0034	Party Name AH
86	Candidate Name 0035	Party Name AI
87	Candidate Name 0035	Party Name AI
88	Candidate Name 0035	Party Name AI
89	Candidate Name 0038	Party Name AL
90	Candidate Name 0039	Party Name AM
91	Candidate Name 0040	Party Name AN
92	Candidate Name 0041	Party Name AO
93	Candidate Name 0042	Party Name AP
94	Candidate Name 0043	Party Name AQ
95	Candidate Name 0044	Party Name AR
96	Candidate Name 0045	Party Name AS
97	Candidate Name 0046	Party Name AT
98	Candidate Name 0047	Party Name AU
99	Candidate Name 0048	Party Name AV
100	Candidate Name 0049	Party Name AW
101	Candidate Name 0050	Party Name AX
102	Candidate Name 0001	Party Name A
103	Candidate Name 0002	Party Name B
104	Candidate Name 0003	Party Name C
105	Candidate Name 0004	Party Name D
106	Candidate Name 0005	Party Name E
107	Candidate Name 0006	Party Name F
108	Candidate Name 0007	Party Name G
109	Candidate Name 0007	Party Name G
110	Candidate Name 0007	Party Name I
111	Candidate Name 0010	Party Name J
112	Candidate Name 0011	Party Name K
113	Candidate Name 0012	Party Name L
114	Candidate Name 0013	Party Name M
115	Candidate Name 0014	Party Name N
116	Candidate Name 0015	Party Name O
117	Candidate Name 0016	Party Name P
118	Candidate Name 0017	Party Name Q
119	Candidate Name 0018	Party Name R
120	Candidate Name 0019	Party Name S
121	Candidate Name 0020	Party Name T
122	Candidate Name 0021	Party Name U
123	Candidate Name 0022	Party Name V
124	Candidate Name 0023	Party Name W
125	Candidate Name 0024	Party Name W
126	Candidate Name 0025	Party Name Y
127	Candidate Name 0026	Party Name Z
128	Candidate Name 0027	Party Name AA
129	Candidate Name 0028	Party Name AB
130	Candidate Name 0029	Party Name AC
131	Candidate Name 0030	Party Name AD
132	Candidate Name 0031	Party Name AE
133	Candidate Name 0032	Party Name AF
134	Candidate Name 0033	Party Name AG
135	Candidate Name 0034	Party Name AH
136	Candidate Name 0035	Party Name AI
137	Candidate Name 0036	Party Name AJ
138
139
140
141
142	Candidate Name 0041	Party Name AO
143	Candidate Name 0042	Party Name AP
144	Candidate Name 0043	Party Name AQ
145	Candidate Name 0044	Party Name AR
146	Candidate Name 0045	Party Name AS
147	Candidate Name 0046	Party Name AT
148	Candidate Name 0047	Party Name AU
149	Candidate Name 0048	Party Name AV
150	Candidate Name 0049	Party Name AW
151	Candidate Name 0050	Party Name AX
Election 2022 (MNA)