• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

New Member
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
 

Attachments

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)
 

Attachments

SUDARSAN

New Member
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
 

Attachments

SUDARSAN

New 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

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
 

Attachments

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

SUDARSAN

New 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 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
 

Attachments

bosco_yip

Excel Ninja
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
 
Last edited:
Status
Not open for further replies.
Top