calculating stock market bonus shares

[mwvirk]

hello

I am calculating how many bonus shares (Flag marked as B) I have received from the stock market for each of my stocks
and then also checking if I sell some bonus shares then how many are still in my portfolio

in this excel file, I have 2 sheets
sheet1 is for buying, selling and bonus shares received
sheet2 is to check the status that how many bonus shares were received and how many are left. if all are sold then I should get 0 in sheet2 cell D
(but in all cases, I want to keep the actual bonus shares received in sheet2 cell C)

I have multiple issues here in this sheet2:

1- if the cell H in sheet1 is blank then cell D in sheet2 is not counting the total (i should have a value of 0 at least in sheet1 cell H to get the result since the blank cell is not showing the count in sheet2 cell D)
2- if I partially or fully sell bonus shares in sheet1 then it's showing balance zero in all cases in sheet2 cell D (i am checking here to know how many are still in my portfolio)

kindly check and help.

thanks

Test-Copy.xlsx
	A	B	C	D	E	F	G	H
1	Stock name		Flag			Received		Sold
2	Apple		R			100		0
3	Google		R			150		0
4	Intel		R			200		0
5	Facebook		R			250		0
6	Apple		B			10		5
7	Google		B			15		0
8	Intel		B			20		0
9	Facebook		B			25		0
10	Apple		R			200		0
11	Google		R			250		0
12	Intel		R			300		0
13	Facebook		R			350		0
14	Apple		B			15
15	Google		B			20
16	Intel		B			25
17	Facebook		B			30
Sheet1

Test-Copy.xlsx
	A	B	C	D
1	Apple		25	0
2	Google		35	15
3	Intel		45	20
4	Facebook		55	25
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B",Sheet1!H:H,0)

 

[mwvirk]

What version of Excel are you using? Please update your profile to show that, as some functions available in Excel 365 might prove useful, either now or in the future...so displaying your version helps others identify solutions that might work better for you.

One thing that jumps out is the extensive use of merged cells in columns A:I and N:Y. Generally it's a bad idea to use them, especially when formulas are involved. Some formulas misbehave with merged cells and it becomes difficult to follow what the formula is doing, Additionally, table filtering and sorting will not be possible. Two other issues:

1. The column P formula uses a fixed reference:

   LARGE($L$2:$L$31,2)

   This fixed reference pointing to $L$2:$L$31 will be carried forward to other record sets when you copy and paste. You probably want a relative reference, such as

   LARGE(L2:L31,2)

2. Same for column Q formula

Let me know about your Excel version. I'm thinking that a flat table might be more robust.
Are you opposed to multiple tables? One table would log voting region (districts, precincts, or provinces) statistics. Another logs statistics for candidates, and another summarizes winners by voting region. That way you wouldn't need merged cells, and you would avoid a lot of redundant information in a large table (which is what will happen if the current merged cell information is copied throughout each "province" block to eliminate the merged cells).

Thanks again Krice,
i have updated my profit with the Office (2016) & OS version (Windows)
i tried using the formulas without '$' and it's still not helping when I copy the first record to downward.
unfortunately i cannot use XL2BB in my office computer (it's blocked)
i will update the mini sheet later today from PC or if there is any other way to share the file?

 

[mwvirk]

i am sharing the screenshot. if you look at the highlighted cells (L35 to L41) the condition format is not working. the highest number is 160,000 and it should be marked as GREEN but it's marking the runner-up (140,000) as GREEN which should be actually RED

 

[mwvirk]

but if you look at row N O P Q, these are showing the correct winner & part name as well as the correct runner-up and part name.

 

[mwvirk]

What version of Excel are you using? Please update your profile to show that, as some functions available in Excel 365 might prove useful, either now or in the future...so displaying your version helps others identify solutions that might work better for you.

One thing that jumps out is the extensive use of merged cells in columns A:I and N:Y. Generally it's a bad idea to use them, especially when formulas are involved. Some formulas misbehave with merged cells and it becomes difficult to follow what the formula is doing, Additionally, table filtering and sorting will not be possible. Two other issues:

1. The column P formula uses a fixed reference:

   LARGE($L$2:$L$31,2)

   This fixed reference pointing to $L$2:$L$31 will be carried forward to other record sets when you copy and paste. You probably want a relative reference, such as

   LARGE(L2:L31,2)

2. Same for column Q formula

Let me know about your Excel version. I'm thinking that a flat table might be more robust.
Are you opposed to multiple tables? One table would log voting region (districts, precincts, or provinces) statistics. Another logs statistics for candidates, and another summarizes winners by voting region. That way you wouldn't need merged cells, and you would avoid a lot of redundant information in a large table (which is what will happen if the current merged cell information is copied throughout each "province" block to eliminate the merged cells).

Election Test-0000.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party	Previous Winner's Name In 2018	Previous Party Name In 2018
2	NA-001	Province-1	Name-001	Area-001	318,150	521,000	5,000	516,000	163.76%	Candidate Name 0001	Party Name AA	1,000	0.19%	Candidate Name 0013	Party Name AM	Candidate Name 0012	Party Name AL			1122	1122	1122	1122	1122
3										Candidate Name 0002	Party Name AB	100,000	19.38%
4										Candidate Name 0003	Party Name AC	1,000	0.19%
5										Candidate Name 0004	Party Name AD	1,000	0.19%
6										Candidate Name 0005	Party Name AE	1,000	0.19%
7										Candidate Name 0006	Party Name AF	1,000	0.19%
8										Candidate Name 0007	Party Name AG	120,000	23.26%
9										Candidate Name 0008	Party Name AH	1,000	0.19%
10										Candidate Name 0009	Party Name AI	1,000	0.19%
11										Candidate Name 0010	Party Name AJ	1,000	0.19%
12										Candidate Name 0011	Party Name AK	1,000	0.19%
13										Candidate Name 0012	Party Name AL	130,000	25.19%
14										Candidate Name 0013	Party Name AM	140,000	27.13%
15										Candidate Name 0014	Party Name AN	1,000	0.19%
16										Candidate Name 0015	Party Name AO	1,000	0.19%
17										Candidate Name 0016	Party Name AP	1,000	0.19%
18										Candidate Name 0017	Party Name AQ	1,000	0.19%
19										Candidate Name 0018	Party Name AR	1,000	0.19%
20										Candidate Name 0019	Party Name AS	1,000	0.19%
21										Candidate Name 0020	Party Name AT	1,000	0.19%
22										Candidate Name 0021	Party Name AU	1,000	0.19%
23										Candidate Name 0022	Party Name AV	1,000	0.19%
24										Candidate Name 0023	Party Name AW	1,000	0.19%
25										Candidate Name 0024	Party Name AX	1,000	0.19%
26										Candidate Name 0025	Party Name AY	1,000	0.19%
27										Candidate Name 0026	Party Name AZ	1,000	0.19%
28										Candidate Name 0027	Party Name BA	1,000	0.19%
29										Candidate Name 0028	Party Name BB	1,000	0.19%
30										Candidate Name 0029	Party Name BC	1,000	0.19%
31										Candidate Name 0030	Party Name BD	1,000	0.19%
32	NA-002	Province-1	Name-002	Area-002	318,150	611,000	5,000	606,000	192.05%	Candidate Name 0001	Party Name AA	1,000	0.19%	Candidate Name 0004	Party Name AD	Candidate Name 0002	Party Name AB
33										Candidate Name 0002	Party Name AB	150,000	29.07%
34										Candidate Name 0003	Party Name AC	130,000	25.19%
35										Candidate Name 0004	Party Name AD	160,000	31.01%
36										Candidate Name 0005	Party Name AE	1,000	0.19%
37										Candidate Name 0006	Party Name AF	1,000	0.19%
38										Candidate Name 0007	Party Name AG	140,000	27.13%
39										Candidate Name 0008	Party Name AH	1,000	0.19%
40										Candidate Name 0009	Party Name AI	1,000	0.19%
41										Candidate Name 0010	Party Name AJ	1,000	0.19%
42										Candidate Name 0011	Party Name AK	1,000	0.19%
43										Candidate Name 0012	Party Name AL	1,000	0.19%
44										Candidate Name 0013	Party Name AM	1,000	0.19%
45										Candidate Name 0014	Party Name AN	1,000	0.19%
46										Candidate Name 0015	Party Name AO	1,000	0.19%
47										Candidate Name 0016	Party Name AP	1,000	0.19%
48										Candidate Name 0017	Party Name AQ	1,000	0.19%
49										Candidate Name 0018	Party Name AR	1,000	0.19%
50										Candidate Name 0019	Party Name AS	1,000	0.19%
51										Candidate Name 0020	Party Name AT	1,000	0.19%
52										Candidate Name 0021	Party Name AU	1,000	0.19%
53										Candidate Name 0022	Party Name AV	1,000	0.19%
54										Candidate Name 0023	Party Name AW	1,000	0.19%
55										Candidate Name 0024	Party Name AX	1,000	0.19%
56										Candidate Name 0025	Party Name AY	1,000	0.19%
57										Candidate Name 0026	Party Name AZ	1,000	0.19%
58										Candidate Name 0027	Party Name BA	1,000	0.19%
59										Candidate Name 0028	Party Name BB	1,000	0.19%
60										Candidate Name 0029	Party Name BC	1,000	0.19%
61										Candidate Name 0030	Party Name BD	1,000	0.19%
62	NA-003	Province-1	Name-003	Area-003						Candidate Name 0001	Party Name AA
63										Candidate Name 0002	Party Name AB
64										Candidate Name 0003	Party Name AC
65										Candidate Name 0004	Party Name AD
66										Candidate Name 0005	Party Name AE
67										Candidate Name 0006	Party Name AF
68										Candidate Name 0007	Party Name AG
69										Candidate Name 0008	Party Name AH
70										Candidate Name 0009	Party Name AI
71										Candidate Name 0010	Party Name AJ
72										Candidate Name 0011	Party Name AK
73										Candidate Name 0012	Party Name AL
74										Candidate Name 0013	Party Name AM
75										Candidate Name 0014	Party Name AN
76										Candidate Name 0015	Party Name AO
77										Candidate Name 0016	Party Name AP
78										Candidate Name 0017	Party Name AQ
79										Candidate Name 0018	Party Name AR
80										Candidate Name 0019	Party Name AS
81										Candidate Name 0020	Party Name AT
82										Candidate Name 0021	Party Name AU
83										Candidate Name 0022	Party Name AV
84										Candidate Name 0023	Party Name AW
85										Candidate Name 0024	Party Name AX
86										Candidate Name 0025	Party Name AY
87										Candidate Name 0026	Party Name AZ
88										Candidate Name 0027	Party Name BA
89										Candidate Name 0028	Party Name BB
90										Candidate Name 0029	Party Name BC
91										Candidate Name 0030	Party Name BD
Election 2022 (MNA)

Cell Formulas
Range	Formula
H2,H32	H2	=SUM(L2:L31)
I2,I32	I2	=IF(E2="","",F2/E2)
M2:M61	M2	=IF(L2="","",L2/$H$2)
N2,N32	N2	=INDEX(J2:J31,MATCH(MAX(L2:L31),L2:L31,0))
O2,O32	O2	=INDEX(K2:K31,MATCH(MAX(L2:L31),L2:L31,0))
P2,P32	P2	=INDEX(J2:J31,MATCH(LARGE(L2:L31,2),L2:L31,0))
Q2,Q32	Q2	=INDEX(K2:K31,MATCH(LARGE(L2:L31,2),L2:L31,0))
F2,F32	F2	=H2+G2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,1))	text	NO
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,2))	text	NO
F32:F61	Cell Value	>$E$2	text	NO
F32:F61	Cell Value	>0	text	NO
H32:H61	Cell Value	>$E$2	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,1))	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,2))	text	NO
F2:F31	Cell Value	>$E$2	text	NO
F2:F31	Cell Value	>0	text	NO
H2:H31	Cell Value	>$E$2	text	NO

 

[mwvirk]

i think problem is only in condition check formating in L & M rows

 

[KRice]

Here is an example of a different format that I mentioned. To avoid the very long blocks for each NA# and the merged cells, the idea here is to:

1. Construct a single list of Candidate Names and the associated Party Name in W2:X50 (this could be longer, but the formulas are set up to accommodate that many...alternatively, you could convert this to an official Excel table, which would dynamically resize itself).
2. Construct the descriptions of the voting regions/provinces/districts (not sure what these are called, but I'm referring to columns A:E and G). You may want to switch the F:G positions so that initial inputs would be columns A:F (the last being Rejected Votes)...because everything to the right is formula-based.
3. Helper cells in Z1:AA3 count the number of voting regions/provinces/districts and the number of candidates. Based on these two counts, we know the vote tabulation table needs to have a specific number of rows given by (#regions) * (#candidates), so formulas in columns Q:S automatically construct the left side of the vote tabulation table...just pull the formulas in Q:S down until blanks are produced. The NA#'s and candidate information automatically populates the table.
4. When votes are entered in column T, column U will calculate and information for the winner and runner up will be determined by formulas in the main table. Importantly, these formulas do not reference a specific range tied to an NA#. Instead the formulas reference the entire voter tabulation table and use logic-based matching to consider only the relevant rows.
5. The issue you described earlier is a Conditional Formatting problem, and I suspect the formula used on your worksheet needs to be revised. In this example, I've added two Conditional Formatting rules to turn the winner vote tally green and the runner up tally red.

I have all of these on the same worksheet, but I'm showing it in sections for easier viewing:
The main table:

Mrexcel_20220918_mwvirk.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party	Previous Winner's Name In 2018	Previous Party Name In 2018
2	NA-001	Province-1	Name-001	Area-001	318150	20920	5000	15920	6.576%	Candidate Name 0004	Party Name AD	Candidate Name 0009	Party Name AI
3	NA-002	Province-1	Name-002	Area-002		126		126		Candidate Name 0009	Party Name AI	Candidate Name 0008	Party Name AH
4	NA-003	Province-1	Name-003	Area-003		28156		28156		Candidate Name 0009	Party Name AI	Candidate Name 0006	Party Name AF
5	NA-004	Province-2	Name-004	Area-004		64170		64170		Candidate Name 0004	Party Name AD	Candidate Name 0002	Party Name AB
6	NA-005	Province-2	Name-005	Area-005		46856		46856		Candidate Name 0007	Party Name AG	Candidate Name 0008	Party Name AH
Election 2022 (MNA)_v2

Cell Formulas
Range	Formula
H2:H6	H2	=SUMIF($Q:$Q,$A2,$T:$T)
I2:I6	I2	=IF($E2="","",$F2/$E2)
J2:J6	J2	=INDEX($R$2:$R$1000,AGGREGATE(15,6,(ROW($2:$1000)-ROW($2:$2)+1)/(($Q$2:$Q$1000=$A2)*($T$2:$T$1000=(LARGE(($T$2:$T$1000)*($Q$2:$Q$1000=$A2),1)))),1))
K2:K6	K2	=INDEX($S$2:$S$1000,AGGREGATE(15,6,(ROW($2:$1000)-ROW($2:$2)+1)/(($Q$2:$Q$1000=$A2)*($T$2:$T$1000=(LARGE(($T$2:$T$1000)*($Q$2:$Q$1000=$A2),1)))),1))
L2:L6	L2	=INDEX($R$2:$R$1000,AGGREGATE(15,6,(ROW($2:$1000)-ROW($2:$2)+1)/(($Q$2:$Q$1000=$A2)*($T$2:$T$1000=(LARGE(($T$2:$T$1000)*($Q$2:$Q$1000=$A2),2)))),1))
M2:M6	M2	=INDEX($S$2:$S$1000,AGGREGATE(15,6,(ROW($2:$1000)-ROW($2:$2)+1)/(($Q$2:$Q$1000=$A2)*($T$2:$T$1000=(LARGE(($T$2:$T$1000)*($Q$2:$Q$1000=$A2),2)))),1))
F2:F6	F2	=SUM($G2:$H2)

The vote tabulation table (only a portion shown):

Mrexcel_20220918_mwvirk.xlsx
	Q	R	S	T	U
1	NA # (2018)	Candidate Name	Party Name	Votes	% Of Candidate Votes
2	NA-001	Candidate Name 0001	Party Name AA	1000	6.281%
3	NA-001	Candidate Name 0002	Party Name AB	500	3.141%
4	NA-001	Candidate Name 0003	Party Name AC	2	0.013%
5	NA-001	Candidate Name 0004	Party Name AD	9000	56.533%
6	NA-001	Candidate Name 0005	Party Name AE	4	0.025%
7	NA-001	Candidate Name 0006	Party Name AF	2700	16.960%
8	NA-001	Candidate Name 0007	Party Name AG	6	0.038%
9	NA-001	Candidate Name 0008	Party Name AH	7	0.044%
10	NA-001	Candidate Name 0009	Party Name AI	2701	16.966%
11	NA-002	Candidate Name 0001	Party Name AA	10	7.937%
12	NA-002	Candidate Name 0002	Party Name AB	11	8.730%
13	NA-002	Candidate Name 0003	Party Name AC	12	9.524%
14	NA-002	Candidate Name 0004	Party Name AD	13	10.317%
15	NA-002	Candidate Name 0005	Party Name AE	14	11.111%
16	NA-002	Candidate Name 0006	Party Name AF	15	11.905%
17	NA-002	Candidate Name 0007	Party Name AG	16	12.698%
18	NA-002	Candidate Name 0008	Party Name AH	17	13.492%
19	NA-002	Candidate Name 0009	Party Name AI	18	14.286%
20	NA-003	Candidate Name 0001	Party Name AA	19	0.067%
21	NA-003	Candidate Name 0002	Party Name AB	20	0.071%
22	NA-003	Candidate Name 0003	Party Name AC	21	0.075%
23	NA-003	Candidate Name 0004	Party Name AD	22	0.078%
24	NA-003	Candidate Name 0005	Party Name AE	23	0.082%
25	NA-003	Candidate Name 0006	Party Name AF	1000	3.552%
26	NA-003	Candidate Name 0007	Party Name AG	25	0.089%
27	NA-003	Candidate Name 0008	Party Name AH	26	0.092%
28	NA-003	Candidate Name 0009	Party Name AI	27000	95.894%
29	NA-004	Candidate Name 0001	Party Name AA	28	0.044%
30	NA-004	Candidate Name 0002	Party Name AB	20000	31.167%
Election 2022 (MNA)_v2

Cell Formulas
Range	Formula
Q2:Q30	Q2	=IF(ROWS(Q$2:Q2)>$AA$2*$AA$3,"",INDEX($A$2:$A$50,INT((ROWS(Q$2:Q2)-1)/$AA$3)+1))
R2:R30	R2	=IF(ROWS(Q$2:Q2)>$AA$2*$AA$3,"",INDEX($W$2:$W$50,MOD(ROWS(Q$2:Q2)-1,$AA$3)+1))
S2:S30	S2	=IF(ROWS(Q$2:Q2)>$AA$2*$AA$3,"",INDEX($X$2:$X$50,MOD(ROWS(R$2:R2)-1,$AA$3)+1))
U2:U30	U2	=IF(T2="","",T2/SUMIF(Q:Q,Q2,T:T))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T2:U50	Expression	=AGGREGATE(14,6,($T$2:$T$1000)/(($Q$2:$Q$1000=$Q2)*($Q2<>"")*($T2<>"")),2)=$T2	text	NO
T2:U50	Expression	=AGGREGATE(14,6,($T$2:$T$1000)/(($Q$2:$Q$1000=$Q2)*($Q2<>"")*($T2<>"")),1)=$T2	text	NO

The candidate information table and helper/counter table:

Mrexcel_20220918_mwvirk.xlsx
	W	X	Y	Z	AA
1	Candidate Name	Party Name		Helper Counts for Convenience
2	Candidate Name 0001	Party Name AA		Number of Voting Districts/Precincts	5
3	Candidate Name 0002	Party Name AB		Number of Candidates	9
4	Candidate Name 0003	Party Name AC
5	Candidate Name 0004	Party Name AD
6	Candidate Name 0005	Party Name AE
7	Candidate Name 0006	Party Name AF
8	Candidate Name 0007	Party Name AG
9	Candidate Name 0008	Party Name AH
10	Candidate Name 0009	Party Name AI
11
Election 2022 (MNA)_v2

Cell Formulas
Range	Formula
AA2	AA2	=COUNTA(A2:A1000)
AA3	AA3	=COUNTA(W2:W1000)

The workbook is available here for convenience:

Mrexcel_20220918_mwvirk.xlsx

Shared with Dropbox

www.dropbox.com

 

[mwvirk]

hello KRice, I really appreciate for giving me your time and sharing your remarkable work. the solution provided in your last post looks very difficult and I am sure you must have spent a lot of time resolving it. I know it must have taken a lot of time. please note that my knowledge of excel is minimal. in my mini sheet, I have shared 3 records but actually, my worksheet has almost 950 records. I have to split these 950 into 2 sheets to divide the category. so 300 will go in 1 sheet and the remaining 650 in another sheet. In the end, I will have a combined dashboard.
At this moment, I am not sure how many candidates will be in 1 record. Based on the confirmation, I have to add/remove the entries.
The logic used in your sheet is really looking excellent but due to my limited knowledge, I don't think I can input the actual record correctly and at that moment I will not have time to revise or recreate a new worksheet.

I don't understand the purpose of helper cells and cell W:X how can I add all 950x30 candidates where the name might be the same in many locations? honestly, it looks great but It's very difficult for me to understand.

there are 2 options:
1 if you can guide me on how to add or remove the candidates without impacting the entire sheet. in your R:S I can add more candidates if I add them in W:X but in your Q I cannot add a new NA # and if I add candidates having the same name then check the below screenshot, then J, K, L, M are giving error.

Other option is, if I can share the actual sheet in private and you can add all records as per your technique and I will do the data entry. but again this will not help me in sheet-2 and dashboard.


so, i am sharing my actual sheet again but this time we will not merge the cells. i have noticed that it will still OK although i have to paste the formula manually.
but still the condition check is not working.

i have another idea, may be this could help us. the value in row A & D are unique. can we use it to solve thge condition check issue and might be used in other rows as well if needed.
e.g. the condition check i am using in having a formula/ may be you can amend this formula and use the unique rows?

i am sharing the mini sheet again after removing the merged cells

(i have removed from rows which are not having significant data. actually it will be input later to check the last year winners)

Election Test-0005.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party
2	NA-001	Province-1	Name-001	Area-001	318,150	670,000	5,000	665,000	210.59%	Candidate Name 0001	Party Name AA	1,000	0.15%	Candidate Name 0014	Party Name AN	Candidate Name 0013	Party Name AM
3										Candidate Name 0002	Party Name AB	100,000	15.04%
4										Candidate Name 0003	Party Name AC	1,000	0.15%
5										Candidate Name 0004	Party Name AD	1,000	0.15%
6										Candidate Name 0005	Party Name AE	1,000	0.15%
7										Candidate Name 0006	Party Name AF	1,000	0.15%
8										Candidate Name 0007	Party Name AG	120,000	18.05%
9										Candidate Name 0008	Party Name AH	1,000	0.15%
10										Candidate Name 0009	Party Name AI	1,000	0.15%
11										Candidate Name 0010	Party Name AJ	1,000	0.15%
12										Candidate Name 0011	Party Name AK	1,000	0.15%
13										Candidate Name 0012	Party Name AL	130,000	19.55%
14										Candidate Name 0013	Party Name AM	140,000	21.05%
15										Candidate Name 0014	Party Name AN	150,000	22.56%
16										Candidate Name 0015	Party Name AO	1,000	0.15%
17										Candidate Name 0016	Party Name AP	1,000	0.15%
18										Candidate Name 0017	Party Name AQ	1,000	0.15%
19										Candidate Name 0018	Party Name AR	1,000	0.15%
20										Candidate Name 0019	Party Name AS	1,000	0.15%
21										Candidate Name 0020	Party Name AT	1,000	0.15%
22										Candidate Name 0021	Party Name AU	1,000	0.15%
23										Candidate Name 0022	Party Name AV	1,000	0.15%
24										Candidate Name 0023	Party Name AW	1,000	0.15%
25										Candidate Name 0024	Party Name AX	1,000	0.15%
26										Candidate Name 0025	Party Name AY	1,000	0.15%
27										Candidate Name 0026	Party Name AZ	1,000	0.15%
28										Candidate Name 0027	Party Name BA	1,000	0.15%
29										Candidate Name 0028	Party Name BB	1,000	0.15%
30										Candidate Name 0029	Party Name BC	1,000	0.15%
31										Candidate Name 0030	Party Name BD	1,000	0.15%
32	NA-002	Province-1	Name-002	Area-002	318,150	927,002	5,000	922,002	291.37%	Candidate Name 0001	Party Name AA	1,000	0.15%	Candidate Name 0010	Party Name AJ	Candidate Name 0006	Party Name AF
33										Candidate Name 0002	Party Name AB	1	0.00%
34										Candidate Name 0003	Party Name AC	1	0.00%
35										Candidate Name 0004	Party Name AD	160,000	24.06%
36										Candidate Name 0005	Party Name AE	1,000	0.15%
37										Candidate Name 0006	Party Name AF	170,000	25.56%
38										Candidate Name 0007	Party Name AG	140,000	21.05%
39										Candidate Name 0008	Party Name AH	130,000	19.55%
40										Candidate Name 0009	Party Name AI	120,000	18.05%
41										Candidate Name 0010	Party Name AJ	180,000	27.07%
42										Candidate Name 0011	Party Name AK	1,000	0.15%
43										Candidate Name 0012	Party Name AL	1,000	0.15%
44										Candidate Name 0013	Party Name AM	1,000	0.15%
45										Candidate Name 0014	Party Name AN	1,000	0.15%
46										Candidate Name 0015	Party Name AO	1,000	0.15%
47										Candidate Name 0016	Party Name AP	1,000	0.15%
48										Candidate Name 0017	Party Name AQ	1,000	0.15%
49										Candidate Name 0018	Party Name AR	1,000	0.15%
50										Candidate Name 0019	Party Name AS	1,000	0.15%
51										Candidate Name 0020	Party Name AT	1,000	0.15%
52										Candidate Name 0021	Party Name AU	1,000	0.15%
53										Candidate Name 0022	Party Name AV	1,000	0.15%
54										Candidate Name 0023	Party Name AW	1,000	0.15%
55										Candidate Name 0024	Party Name AX	1,000	0.15%
56										Candidate Name 0025	Party Name AY	1,000	0.15%
57										Candidate Name 0026	Party Name AZ	1,000	0.15%
58										Candidate Name 0027	Party Name BA	1,000	0.15%
59										Candidate Name 0028	Party Name BB	1,000	0.15%
60										Candidate Name 0029	Party Name BC	1,000	0.15%
61										Candidate Name 0030	Party Name BD	1,000	0.15%
62	NA-003	Province-1	Name-003	Area-003						Candidate Name 0001	Party Name AA	10	1.504E-05	Candidate Name 0030	Party Name BD	Candidate Name 0029	Party Name BC
63										Candidate Name 0002	Party Name AB	20	3.008E-05
64										Candidate Name 0003	Party Name AC	30	4.511E-05
65										Candidate Name 0004	Party Name AD	40	6.015E-05
66										Candidate Name 0005	Party Name AE	50	7.519E-05
67										Candidate Name 0006	Party Name AF	60	9.023E-05
68										Candidate Name 0007	Party Name AG	70	0.0001053
69										Candidate Name 0008	Party Name AH	80	0.0001203
70										Candidate Name 0009	Party Name AI	90	0.0001353
71										Candidate Name 0010	Party Name AJ	100	0.0001504
72										Candidate Name 0011	Party Name AK	110	0.0001654
73										Candidate Name 0012	Party Name AL	120	0.0001805
74										Candidate Name 0013	Party Name AM	130	0.0001955
75										Candidate Name 0014	Party Name AN	140	0.0002105
76										Candidate Name 0015	Party Name AO	150	0.0002256
77										Candidate Name 0016	Party Name AP	160	0.0002406
78										Candidate Name 0017	Party Name AQ	170	0.0002556
79										Candidate Name 0018	Party Name AR	180	0.0002707
80										Candidate Name 0019	Party Name AS	190	0.0002857
81										Candidate Name 0020	Party Name AT	200	0.0003008
82										Candidate Name 0021	Party Name AU	210	0.0003158
83										Candidate Name 0022	Party Name AV	220	0.0003308
84										Candidate Name 0023	Party Name AW	230	0.0003459
85										Candidate Name 0024	Party Name AX	240	0.0003609
86										Candidate Name 0025	Party Name AY	250	0.0003759
87										Candidate Name 0026	Party Name AZ	260	0.000391
88										Candidate Name 0027	Party Name BA	270	0.000406
89										Candidate Name 0028	Party Name BB	280	0.0004211
90										Candidate Name 0029	Party Name BC	290	0.0004361
91										Candidate Name 0030	Party Name BD	300	0.0004511
Election 2022 (MNA)

Cell Formulas
Range	Formula
F2	F2	=$H2+$G2
H2	H2	=SUM($L2:$L31)
I2	I2	=IF($E2="","",$F2/$E2)
N2,N62,N32	N2	=INDEX($J2:$J31,MATCH(MAX($L2:$L31),$L2:$L31,0))
O2	O2	=INDEX($K$2:$K31,MATCH(MAX($L2:$L31),$L2:$L31,0))
P2	P2	=INDEX($J2:$J31,MATCH(LARGE($L2:$L31,2),$L2:$L31,0))
Q2	Q2	=INDEX($K2:$K31,MATCH(LARGE($L2:$L31,2),$L2:$L31,0))
F32	F32	=H32+G32
H32	H32	=SUM(L32:L61)
I32	I32	=IF(E32="","",F32/E32)
O32,O62	O32	=INDEX(K32:K61,MATCH(MAX(L32:L61),L32:L61,0))
P32,P62	P32	=INDEX(J32:J61,MATCH(LARGE(L32:L61,2),L32:L61,0))
Q32,Q62	Q32	=INDEX(K32:K61,MATCH(LARGE(L32:L61,2),L32:L61,0))
M2	M2	=IF($L2="","",$L2/$H2)
M3:M31	M3	=IF($L3="","",$L3/$H$2)
M32:M91	M32	=IF(L32="","",L32/$H$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,1))	text	NO
L32:M61	Expression	=OR(L32=MAX(L$2:L$31),L32=LARGE(L$2:L$31,2))	text	NO
F32:F61	Cell Value	>$E$2	text	NO
F32:F61	Cell Value	>0	text	NO
H32:H61	Cell Value	>$E$2	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,1))	text	NO
L2:M31	Expression	=OR(L2=MAX(L$2:L$31),L2=LARGE(L$2:L$31,2))	text	NO
F2:F31	Cell Value	>$E$2	text	NO
F2:F31	Cell Value	>0	text	NO
H2:H31	Cell Value	>$E$2	text	NO

please take your time and work on this when you are free.
once again I am thankful for your excellent guidenace.

Regards,
MWVirk

 

[KRice]

my worksheet has almost 950 records. I have to split these 950 into 2 sheets to divide the category. so 300 will go in 1 sheet and the remaining 650 in another sheet.

Can you tell me more about the reason for splitting the records? That creates a complication, but I don't understand what needs to happen with the two groupings. How is the split determined?

I don't understand the purpose of helper cells and cell W:X how can I add all 950x30 candidates where the name might be the same in many locations? honestly, it looks great but It's very difficult for me to understand.

The helper cells in Z:AA simply provide a count of the number of candidates and the number of NA# items. I believe all of the candidates are to be shown for each NA#...is that correct? If so, then if you have 950 NA# items and 30 candidates, the entire list would require 950*30=28500 rows.

1 if you can guide me on how to add or remove the candidates without impacting the entire sheet. in your R:S I can add more candidates if I add them in W:X but in your Q I cannot add a new NA # and if I add candidates having the same name then check the below screenshot, then J, K, L, M are giving error.

The candidates and their party affiliations are input only in W:X...no where else. Any new NA# items are added in A:D...just keep adding more items to the bottom of the main table. So the helper cells in Z:AA count the number of NA# items appearing in column A and the number of candidates appears in column W...and based on those two counts, formulas in columns Q,R,S automatically create the voting tabulation table. You do not enter anything in the Q:S columns because the formulas will automatically repeat the list of candidates and party names shown in W:X...and this is done for every NA# shown in column A. So if you learn that there will be 20 candidates, you list those 20 candidates in W:X. You would also fill out the main table to show all of the NA# items (columns A:D). Then select the bottom row of the voting tabulation table, say cells Q50:U50 and drag those formulas down until they produce blanks. That will automatically create the combinations of NA#s and candidate groupings.

i have another idea, may be this could help us. the value in row A & D are unique. can we use it to solve thge condition check issue

If I understand your point, this is what I've done in column Q of the voting tabulation table. The unique NA# is used to "tag" each grouping of candidate/party names. And column Q is then used in the J:M formulas to ensure that we are only considering votes for each NA# shown (under column A) of the main table.

About those errors:
Some adjustment in the J:M formulas are needed. The #NUM! errors are caused by having no rows in the voting tabulation table that correspond to the NA#. If the formulas in Q:U are pulled down until blanks are produced in those cells, then the voting tabulation table has reached its maximum length and the #NUM! errors should go away. However, there is still an issue: if votes are not entered in the voting tabulation table, the formulas in J:M will report the winner and runner up as the first candidate for each NA# (where no votes are recorded for that NA#). This issue can be fixed, but the solution for it depends on what you want. For example, do you want the J:M columns to display the 1st and 2nd place vote winners as the votes are entered, or do you want to wait until all votes have been tabulated for that NA#. I would probably opt for the 2nd to avoid misunderstandings about declaring a winner and runner up before votes have been entered for each of the candidates.

In terms of usability, I would probably move the voting tabulation table to a separate sheet and use autofilters on the columns so that you could quickly isolate the candidate groupings for any given NA# and then enter the actual votes (unless this is somehow to be done automatically).

 

[mwvirk]

Can you tell me more about the reason for splitting the records? That creates a complication, but I don't understand what needs to happen with the two groupings. How is the split determined?

i have multiple records but all are unique.
1- NA-001 to NA-280 (i can write in any format like NA001 or NA0001 NA-#0001 anything as long as it's having "NA & 1") these record will go up to 280 but still possibilities are then might be less but not more than 280
2- PP-001 to PP-500 (same scenario here like NA - write in any format, number can be increased or decreased)
3- PS-001 to PS-200 (same scenario like PP)
4- PB-001 to PB-100) (same scenario like PP)
5- KP-001 to KP-150) (same scenario like PP)

but, whatever is all NA, PP, PS, PB, KP are unique

The candidates and their party affiliations are input only in W:X.

the more i add in W:X it's keep on increasing the Q with same NA

 

[mwvirk]

i tried my existing sheet and everything is working fine even with merged cell or without merging the cell.
only issue is the condition check is not working to highlight the winner name and party as well as the runner-up name and party

if for some reason, by using the unique values, (as mentioned above NA PS PB KP PP) we can update the formula in condition check then this problem can be resolved and i will start working on dashboard

Thank you once again @KRice.