# How to put a formula/function for completion on n th row it should RETURN TO 1st row and repeat till n AGAIN go to 1st repeat till n so on

Status
Not open for further replies.

#### SUDARSAN

##### Member
DEAR FRIENDS ,

How to put a formula/function for "completion on n th row it should RETURN TO 1st row and repeat till n, AGAIN go to 1st repeat till n , so on up to given times or x times"

regards
SUDARSAN

#### Attachments

• 14.8 KB Views: 14

#### Excel Wizard

##### Active Member
for only 1 alphabet
=MID(CONCAT(REPT(B4:B7,C4:C7)),MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7))+1,1)
9 is repeat times

for more than 1 alphabet
=INDEX(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&B4:B7,C4:C7)),"/m","x",1)&"</m></x>","//m"),MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7))+1)

#### Attachments

• 17.7 KB Views: 8

#### SUDARSAN

##### Member
for only 1 alphabet
=MID(CONCAT(REPT(B4:B7,C4:C7)),MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7))+1,1)
9 is repeat times

for more than 1 alphabet
=INDEX(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&B4:B7,C4:C7)),"/m","x",1)&"</m></x>","//m"),MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7))+1)

Thanks a lot , I heartily appreciate your help

I downloaded the sheet, it is working fine for single alphabet but for more than 1 alphabet not working, because FilterXML not in my Mac office 365 version

showing error #NAME?

#### Attachments

• 17.7 KB Views: 2

#### Excel Wizard

##### Active Member
Try this

=LOOKUP(MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7)),MMULT(N(ROW(C4:C7)>TRANSPOSE(ROW(C4:C7))),C4:C7),B4:B7)

#### Attachments

• 18.5 KB Views: 5

#### SUDARSAN

##### Member
Try this

=LOOKUP(MOD(SEQUENCE(SUM(C4:C7)*9,,0),SUM(C4:C7)),MMULT(N(ROW(C4:C7)>TRANSPOSE(ROW(C4:C7))),C4:C7),B4:B7)
Thank you dear ,
it is working

I don't know how to express my gratitudes

Thanks a lot
and can you please have a look on below attached an excel sheet, and please suggest any function if possible

thank you once again

regards
SUDARSAN

#### Attachments

• 23.6 KB Views: 14

#### Excel Wizard

##### Active Member
=LOOKUP(MOD(SEQUENCE(SUM(F7:F10)*A9,,0),SUM(F7:F10)),MMULT(N(ROW(F7:F10)>TRANSPOSE(ROW(F7:F10))),F7:F10),E7:E10)

#### SUDARSAN

##### Member
=LOOKUP(MOD(SEQUENCE(SUM(F7:F10)*A9,,0),SUM(F7:F10)),MMULT(N(ROW(F7:F10)>TRANSPOSE(ROW(F7:F10))),F7:F10),E7:E10)
Thank you very much

sequence is not working in my excel 2016 , is there any alternative

thanking you
Regards
SUDARSAN

#### Excel Wizard

##### Active Member
J16
=LOOKUP(MOD(ROWS(J\$16:J16)-1,SUM(\$F\$7:\$F\$10)),MMULT(N(ROW(\$F\$7:\$F\$10)>TRANSPOSE(ROW(\$F\$7:\$F\$10))),\$F\$7:\$F\$10),\$E\$7:\$E\$10)

Press Ctrl+Shift+Enter and copy down

#### SUDARSAN

##### Member
J16
=LOOKUP(MOD(ROWS(J\$16:J16)-1,SUM(\$F\$7:\$F\$10)),MMULT(N(ROW(\$F\$7:\$F\$10)>TRANSPOSE(ROW(\$F\$7:\$F\$10))),\$F\$7:\$F\$10),\$E\$7:\$E\$10)

Press Ctrl+Shift+Enter and copy down

Thank you dear

Thanks a lot
I will try it

#### SUDARSAN

##### Member
J16
=LOOKUP(MOD(ROWS(J\$16:J16)-1,SUM(\$F\$7:\$F\$10)),MMULT(N(ROW(\$F\$7:\$F\$10)>TRANSPOSE(ROW(\$F\$7:\$F\$10))),\$F\$7:\$F\$10),\$E\$7:\$E\$10)

Press Ctrl+Shift+Enter and copy down
getting #value! error

#### Peter Bartholomew

##### Well-Known Member
In 365 the formula can look more like code
Code:
``````= LET(
m, SUM(Tbl[Repeat times]),
k, 1+MOD(SEQUENCE(m*cycles,,0),m),
threshold, SUMIFS(Tbl[Repeat times],Tbl[HPR],"<="&Tbl[HPR]),
idx, XMATCH(k,threshold,1),
INDEX(Tbl[HPR],idx))``````

#### herofox

##### Active Member
As You See Function for
Excel Wizard
work perfect you Must to press (Ctrl+Shift+Enter)Not Enter Only

#### Attachments

• 29.5 KB Views: 2

#### SUDARSAN

##### Member
In 365 the formula can look more like code
Code:
``````= LET(
m, SUM(Tbl[Repeat times]),
k, 1+MOD(SEQUENCE(m*cycles,,0),m),
threshold, SUMIFS(Tbl[Repeat times],Tbl[HPR],"<="&Tbl[HPR]),
idx, XMATCH(k,threshold,1),
INDEX(Tbl[HPR],idx))``````
Thank you dear Peter ,

I tried to repeat value that MATCH and REPEAT

with Index(x:Y, match(x,x;y,0)) but it is not returning the multiple value

IF value is >= C4.. it should return Major first and ALL in next cell, IF value is <= C13... it should return ALL only

A26: N rows is lookup value with \$A\$4 : \$A\$23 and return \$B4 times of \$D\$ 4: \$D\$23 Value in B26: N rows matching with A4:A23

and

according to the F4:F23, H4:AD23 match and transpose

for example
Plough
Arecanut
Banana
Cashewnut
like so
Cultivator
Arecanut
Banana
Cashewnut
like so

#### Attachments

• 14.9 KB Views: 1

#### bosco_yip

##### Excel Ninja
Thank you dear Peter ,

I tried to repeat value that MATCH and REPEAT

with Index(x:Y, match(x,x;y,0)) but it is not returning the multiple value

IF value is >= C4.. it should return Major first and ALL in next cell, IF value is <= C13... it should return ALL only

A26: N rows is lookup value with \$A\$4 : \$A\$23 and return \$B4 times of \$D\$ 4: \$D\$23 Value in B26: N rows matching with A4:A23

and

according to the F4:F23, H4:AD23 match and transpose

for example
Plough
Arecanut
Banana
Cashewnut
like so
Cultivator
Arecanut
Banana
Cashewnut
like so