deduction start and end consecutive numbers

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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
Excel 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)))
Excel Formula:
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
ABCDEFGHIJKLMNOP
1GrpSE???GrpSEDESD
2A159A1591019
3B7121361091019
4C91415131527401327
5A6109162027401327
6A131527212527401327
7B131713263027401327
8C152415313527401327
9A162027364027401327
10C304010434517604317
11A212527465017604317
12A263027515517604317
13B182013566017604317
14C45505
15A313527C7121320713
16B23271213171320713
17C55601518201320713
18A364027232712352312
19C617015283512352312
20A4345173740340373
21B283512
22B37403A9141524915
23A46501715241524915
24C758015304010403010
25A5155174550550455
26C819015556015705515
27A566017617015705515
28758015907515
29819015907515
Sheet1
Cell Formulas
RangeFormula
M2:M13M2=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:N3N2=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:N13N4=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:K3K2=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:K13K4=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:P13P2=M2-N2
M15:M20M15=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:N16N15=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:N20N17=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:K16K15=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:K20K17=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:M29M22=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:N23N22=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:N29N24=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:K23K22=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:K29K24=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))))
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top