CODEWITHSUNDEEP
excel
Supernova
Supernova

Reputation: 25

Incrementing multiple sequence of numbers in single excel column

I wanted to run multiple sequences of numbers in a single column in excel sheet. For example, this is how a sample output should look like :

enter image description here

As you can see, there are two separate sequences of numbers that are running in single column. One starts from "1" and another starts from "100". What formula can I write in excel such that excel leaves a gap of two rows after "1" and "2" and continues to "3" and "4" and so on.

In this example, I have shown two sequences of numbers which are running in a single column, how can I extend that for 3 or 4 sequences etc.

Upvotes: 1

Views: 465

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34440

Here is a draft of how it might look. Basically taking the row number and looking up the corresponding start value, but subtracting out the terms belonging to the other series (two in this case) and subtracting out the rows before it in the current iteration of the series:

=INDEX(A$2:A$4,MATCH(MOD(ROW()-2,9),C$2:C$4))+ROW()-2
-QUOTIENT(ROW()-2,9)*SUMPRODUCT(B$2:B$4*((ROW(B$2:B$4)-1)<>MATCH(MOD(ROW()-2,9),C$2:C$4)))
-INDEX(C$2:C$4,MATCH(MOD(ROW()-2,9),C$2:C$4))

Can be developed into a spill formula in O365 and tidied up removing the helper column and the hard-coded '9' and using Sequence and Let.

enter image description here

So the whole thing in O365 would be

=LET(startRange,A2:A4,
countRange,B2:B4,
cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange),
rowSeq,SEQUENCE(100,1,0),
totCount,SUM(countRange),
modRow,MOD(rowSeq,totCount),
series,MATCH(modRow,cumTotal),
startValue,INDEX(startRange,series),
countForSeries,INDEX(countRange,series),
quotient,QUOTIENT(rowSeq,totCount),
subtract1,quotient*(totCount-countForSeries),
subtract2,INDEX(cumTotal,series),
startValue+rowSeq-subtract1-subtract2)

Upvotes: 1

Related Questions