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

[SUDARSAN]

DEAR FRIENDS ,
I need your help to complete a task below

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"

please help me friends

Thanking you in advance
your help will be appreciated

regards
SUDARSAN

 

[Excel Wizard]

Please try
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)

 

[SUDARSAN]

Please try
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?

what to do ? Can you please help me in this regards

 

[Excel Wizard]

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)

 

[SUDARSAN]

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

I cordially appreciate your help
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

 

[Excel Wizard]

=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]

=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

please help

thanking you
Regards
SUDARSAN

 

[Excel Wizard]

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]

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]

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

can you please

 

[Peter Bartholomew]

In 365 the formula can look more like 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]

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

 

[SUDARSAN]

In 365 the formula can look more like 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 have one more task can you please help me ,

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
seeking your help on this

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


please help

Thank you in advance

 

[bosco_yip]

Thank you dear Peter ,

I have one more task can you please help me ,

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
seeking your help on this

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


please help

Thank you in advance

New question new post,

Please follow your opened new post as in :

Seeking help on how to use IF ,Index+match transpose | Chandoo.org Excel Forums - Become Awesome in Excel