• 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.

Repeat row values in a specific number of times with specific pattern

syp1677

Member
Please find attached a sample file.
Based on the cell range B2:C13 (with the understanding that some cells B4:C13 are blank), I want to have a specific pattern as described.
I am using Excel 2016, hence some of the functions of 365 won't work in mine.

Your help is highly appreciated.

Regard,
S
 

Attachments

  • Sample14012023.xlsx
    9.4 KB · Views: 4
Try to set up as per attached file.

1] Give criteria of "Min. repeated number of times" :1, 3 and 9

2] Result of "repeated number of 1" in G2 formula:

=IF(ROW(A1)<=SUM($C$2:$C$13),INDEX($B$2:$B$13,MOD(ROW(A1)-1,COUNTA($B$2:$B$13))+1),"")

3] Result of "repeated number of 3" in K2 formula and copied right to O2:

=IF(ROW(A1)<=SUM($C$2:$C$13),INDEX($B$2:$B$13,INT(MOD((ROW(A1)-1)/I$2,I$2))+1),"")

All copied down.

82445
 

Attachments

  • Serial Number.xlsx
    14.1 KB · Views: 2
Thank you Bosco_yip for the response. Great formulaes...
I have encountered one problem if the B4:B13 have formulaes then column G shows different results.
82450
 

Attachments

  • Serial Number.xlsx
    14.2 KB · Views: 1
If B4:B13 have formulae and contain formula blank.

In G2, try to use this copy down formula instead:

=IF(ROW(A1)<=SUM($C$2:$C$13),INDEX($B$2:$B$13,MOD(ROW(A1)-1,COUNTIF($B$2:$B$13,"?*"))+1),"")
 
If B4:B13 have formulae and contain formula blank.

In G2, try to use this copy down formula instead:

=IF(ROW(A1)<=SUM($C$2:$C$13),INDEX($B$2:$B$13,MOD(ROW(A1)-1,COUNTIF($B$2:$B$13,"?*"))+1),"")
Thank you Bosco_yip
It worked perfectly fine.
Explanation of the said formulae will be highly appreciated..

Thanks a tons..
Regards,
S
 
Good catch, please find the revised as in:

1] In K2 revised formula copied down, and copied formula to column O:

=IF(ROW($A1)<=SUM($C$2:$C$13),INDEX($B$2:$B$13,INT(MOD((ROW($A1)-1)/I$2,COUNTIF($B$2:$B$13,"?*")))+1),"")

2] G2, formula not required array entry.

3] Please see revised file

82466
 

Attachments

  • Serial Number (1 revised).xlsx
    14.4 KB · Views: 3
Back
Top