How to be obtained used answer sheets serial numbers

[Farooqui Noor]

Dear Sir,
I work in the exam cell, where my job is to keep track of the serial numbers of the answer sheets sent to exam halls. I have created an Excel sheet for this purpose, which I'm attaching for your reference. In column B, the block numbers are listed. The serial numbers of the answer sheets sent to the exam halls are in columns C and D. After the exam ends, the serial numbers of the answer sheets returned from the exam halls are in columns G and H.

The number of exam blocks can vary each day—sometimes 10, sometimes 12, or even 15 blocks. In cell N4, the serial numbers of all the answer sheets issued are listed in this format: 27223053-27223072,27223075-27223082. Similarly, the serial numbers of the returned answer sheets are listed in cell R4. The serial numbers of the supplements, highlighted in blue, are in cells N8 and R8.

In the yellow highlighted cells, U4 and U8, I need to write the serial numbers of the used answer sheets in the same format as shown in N4 and R4.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	Sr.No.	Block No.	Sr. No. of Answer Books issued to examinees in this session				Sr. No. of Answer Books remains unused in this session				Total Answer Books Used
3			From	To	Total	Grand Total	From	To	Total	Grand Total				issued Answer Books Serial Numbers				Return Answer Books Serial Numbers from exam Hall			Used Answer Books Serial Numbers
4	1	106	27223053	27223072	20	28	27223072	27223072	1	3	25		27223053-27223072,	27223053-27223072,27223075-27223082,--1,--1,27223085-27223109,27223109-27223108,	53		27223072-27223072,	27223072-27223072,27223078-27223079,--1,--1,--1,--1,	3		N7-R7
5			27223075	27223082	8		27223078	27223079	2				27223075-27223082,				27223078-27223079,
6				-1				-1					--1,				--1,
7				-1				-1					--1,	issued Supplements Serial Numbers			--1,	Return Supplements Serial Numbers from exam Hall			Used Supplements Serial Numbers
8			251820	251824	5	5	251823	251824	2	2	3		251820-251824,	251820-251824,--1,--1,--1,	5		251823-251824,	251823-251824,--1,--1,--1,	2		N11-R11
9				-1				-1					--1,				--1,
10				-1				-1					--1,				--1,
11	2	107	27223085	27223109	25	25		-1		0	25		27223085-27223109,				--1,
12			27223109	27223108				-1					27223109-27223108,				--1,
13				-1		0		-1		0	0		--1,				--1,
14	Total Number of Issued Main 32 Pages Answer Books					53	Returned Main Answer Books			3	50
15	Total Number of Issued Supplements					5	Returned Supplements			2	3
16	Total Number of Issued Main 32 Pages Answer Books & Supplements					58	Total Returned			5	53
17
1

Cell Formulas
Range	Formula
F4,J4	F4	=SUM(E4:E7)
N4,R4	N4	=IFERROR(CONCATENATE(M4,""), "") & IFERROR(CONCATENATE(M5,""), "") & IFERROR(CONCATENATE(M6,""), "")& IFERROR(CONCATENATE(M7,""), "")& IFERROR(CONCATENATE(M11,""), "") & IFERROR(CONCATENATE(M12,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")
O4,S4	O4	=F14
F8,J8	F8	=SUM(E8:E10)
N8,R8	N8	=IFERROR(CONCATENATE(M8,""), "") & IFERROR(CONCATENATE(M9,""), "") & IFERROR(CONCATENATE(M10,""), "")& IFERROR(CONCATENATE(M13,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "") & IFERROR(CONCATENATE(#REF!,""), "")& IFERROR(CONCATENATE(#REF!,""), "")
O8,S8	O8	=F15
F11,J11	F11	=SUM(E11:E12)
K4,K8,K11,K13:K16	K4	=SUM(F4-J4)
D4:D13,H4:H13	D4	=SUM(C4+E4)-1
J13,F13	J13	=SUM(I13:I13)
J14	J14	=SUM(AA4:AA13)
J15	J15	=SUM(AB7:AB13)
J16,F16	J16	=SUM(J14:J15)
M4:M13,Q4:Q13	M4	=CONCATENATE(C4,"-",D4,",")
F14	F14	=SUM(Y4:Y13)
F15	F15	=SUM(Z7:Z13)

 

[Farooqui Noor]

Dear Sir,
I issued a total of 53 answer sheets in both block numbers 106 and 107. The serial numbers of these sheets are written in cell N4. Three answer sheets were returned from the exam hall, and their serial numbers are noted in cell R4. This means that on that day, 53 minus 3 equals 50, so a total of 50 answer sheets were used. I need to write the serial numbers of these 50 answer sheets in cell U4, which means subtracting the number in cell R4 from the number in cell N4. Similarly, on that day, a total of 5 supplements were issued, noted in cell N8, and 2 supplements were returned, noted in cell R8. This means that 5 minus 2 equals 3, so a total of 3 supplements were used. I need to write the serial numbers of these 3 supplements in cell U8, which means subtracting the number in cell R8 from the number in cell N8. Remember, the method of writing serial numbers should be the same as in cell N4 and cell R4.