deduction start and end consecutive numbers

[Dossfm0q]

I want the results in column "E" to be the same as the results in column "K", which have two formulas that calculate the deduction as shown in start Column "N"and end Column "M" consecutive numbers in the series for each group

and summarize both Formula

INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1)))

INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1))))

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Grp	S	E		???		Grp	S	E		D		E	S		D
2	A	1	5		9		A	1	5		9		10	1		9
3	B	7	12		13			6	10		9		10	1		9
4	C	9	14		15			13	15		27		40	13		27
5	A	6	10		9			16	20		27		40	13		27
6	A	13	15		27			21	25		27		40	13		27
7	B	13	17		13			26	30		27		40	13		27
8	C	15	24		15			31	35		27		40	13		27
9	A	16	20		27			36	40		27		40	13		27
10	C	30	40		10			43	45		17		60	43		17
11	A	21	25		27			46	50		17		60	43		17
12	A	26	30		27			51	55		17		60	43		17
13	B	18	20		13			56	60		17		60	43		17
14	C	45	50		5
15	A	31	35		27		C	7	12		13		20	7		13
16	B	23	27		12			13	17		13		20	7		13
17	C	55	60		15			18	20		13		20	7		13
18	A	36	40		27			23	27		12		35	23		12
19	C	61	70		15			28	35		12		35	23		12
20	A	43	45		17			37	40		3		40	37		3
21	B	28	35		12
22	B	37	40		3		A	9	14		15		24	9		15
23	A	46	50		17			15	24		15		24	9		15
24	C	75	80		15			30	40		10		40	30		10
25	A	51	55		17			45	50		5		50	45		5
26	C	81	90		15			55	60		15		70	55		15
27	A	56	60		17			61	70		15		70	55		15
28								75	80		15		90	75		15
29								81	90		15		90	75		15
Sheet1

Cell Formulas
Range	Formula
M2:M13	M2	=INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1)))
N2:N3	N2	=INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1))))
N4:N13	N4	=INDEX($H$2:$H$13,IF(ROWS($H$2:H4)=1,1,IF(SUMPRODUCT(--($H$3:H4-$I$2:I3<>1))=0,1,AGGREGATE(14,6,(ROW($H$3:H4)-ROW($H$3)+1)*(--($H$3:H4-$I$2:I3<>1))+1,1))))
K2:K3	K2	=INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1)))-INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1))))
K4:K13	K4	=INDEX($I4:I$13,IF(SUMPRODUCT(--(H5:$H$14-I4:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H4:$H$13)-ROW($H4)+1)/(--(H5:$H$14-I4:$I$13<>1)),1)))-INDEX($H$2:$H$13,IF(ROWS($H$2:H4)=1,1,IF(SUMPRODUCT(--($H$3:H4-$I$2:I3<>1))=0,1,AGGREGATE(14,6,(ROW($H$3:H4)-ROW($H$3)+1)*(--($H$3:H4-$I$2:I3<>1))+1,1))))
P22:P29,P15:P20,P2:P13	P2	=M2-N2
M15:M20	M15	=INDEX($I15:I$20,IF(SUMPRODUCT(--(H16:$H$21-I15:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H15:$H$20)-ROW($H15)+1)/(--(H16:$H$21-I15:$I$20<>1)),1)))
N15:N16	N15	=INDEX($H$15:$H$20,IF(ROWS($H$15:H15)=1,1,IF(SUMPRODUCT(--($H15:H$16-$I14:I$15<>1))=0,1,AGGREGATE(14,6,(ROW($H15:H$16)-ROW($H$16)+1)*(--($H15:H$16-$I14:I$15<>1))+1,1))))
N17:N20	N17	=INDEX($H$15:$H$20,IF(ROWS($H$15:H17)=1,1,IF(SUMPRODUCT(--($H$16:H17-$I$15:I16<>1))=0,1,AGGREGATE(14,6,(ROW($H$16:H17)-ROW($H$16)+1)*(--($H$16:H17-$I$15:I16<>1))+1,1))))
K15:K16	K15	=INDEX($I15:I$20,IF(SUMPRODUCT(--(H16:$H$21-I15:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H15:$H$20)-ROW($H15)+1)/(--(H16:$H$21-I15:$I$20<>1)),1)))-INDEX($H$15:$H$20,IF(ROWS($H$15:H15)=1,1,IF(SUMPRODUCT(--($H15:H$16-$I14:I$15<>1))=0,1,AGGREGATE(14,6,(ROW($H15:H$16)-ROW($H$16)+1)*(--($H15:H$16-$I14:I$15<>1))+1,1))))
K17:K20	K17	=INDEX($I17:I$20,IF(SUMPRODUCT(--(H18:$H$21-I17:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H17:$H$20)-ROW($H17)+1)/(--(H18:$H$21-I17:$I$20<>1)),1)))-INDEX($H$15:$H$20,IF(ROWS($H$15:H17)=1,1,IF(SUMPRODUCT(--($H$16:H17-$I$15:I16<>1))=0,1,AGGREGATE(14,6,(ROW($H$16:H17)-ROW($H$16)+1)*(--($H$16:H17-$I$15:I16<>1))+1,1))))
M22:M29	M22	=INDEX($I22:I$29,IF(SUMPRODUCT(--(H23:$H$30-I22:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H22:$H$29)-ROW($H22)+1)/(--(H23:$H$30-I22:$I$29<>1)),1)))
N22:N23	N22	=INDEX($H$22:$H$29,IF(ROWS($H$22:H22)=1,1,IF(SUMPRODUCT(--($H22:H$23-$I21:I$22<>1))=0,1,AGGREGATE(14,6,(ROW($H22:H$23)-ROW($H$23)+1)*(--($H22:H$23-$I21:I$22<>1))+1,1))))
N24:N29	N24	=INDEX($H$22:$H$29,IF(ROWS($H$22:H24)=1,1,IF(SUMPRODUCT(--($H$23:H24-$I$22:I23<>1))=0,1,AGGREGATE(14,6,(ROW($H$23:H24)-ROW($H$23)+1)*(--($H$23:H24-$I$22:I23<>1))+1,1))))
K22:K23	K22	=INDEX($I22:I$29,IF(SUMPRODUCT(--(H23:$H$30-I22:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H22:$H$29)-ROW($H22)+1)/(--(H23:$H$30-I22:$I$29<>1)),1)))-INDEX($H$22:$H$29,IF(ROWS($H$22:H22)=1,1,IF(SUMPRODUCT(--($H22:H$23-$I21:I$22<>1))=0,1,AGGREGATE(14,6,(ROW($H22:H$23)-ROW($H$23)+1)*(--($H22:H$23-$I21:I$22<>1))+1,1))))
K24:K29	K24	=INDEX($I24:I$29,IF(SUMPRODUCT(--(H25:$H$30-I24:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H24:$H$29)-ROW($H24)+1)/(--(H25:$H$30-I24:$I$29<>1)),1)))-INDEX($H$22:$H$29,IF(ROWS($H$22:H24)=1,1,IF(SUMPRODUCT(--($H$23:H24-$I$22:I23<>1))=0,1,AGGREGATE(14,6,(ROW($H$23:H24)-ROW($H$23)+1)*(--($H$23:H24-$I$22:I23<>1))+1,1))))

 

[GlennUK]

Why don't you put entries in column J to use as keys to fetch data from column K, like A1/A2/A3 for the first 3 cells, and changing to B from rows 15 to 20 and C for rows 22 to 29. Use letter in column A with count of that letter so far to look up what you want.