Help with dynamic formula that prints sequence of repeating text

locksmith55

New Member
Joined
Mar 6, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I am trying to make a formula that will dynamic print sequence of repeating text where I can specify number of rows I want to print for every text. I tried assigning the text values and using small() and sequence() but it didnt work. Is there any way of doing that dont require a too complexe solution?

I appreaiacte any help I can get. I use MS365 office.
Book1
ABCD
1Numbers of rows to repeat5
2Apples
3Pears
4Bananas
5
6How I want it to look
7Apples
8Apples
9Apples
10Apples
11Apples
12Pears
13Pears
14Pears
15Pears
16Pears
17Bananas
18Bananas
19Bananas
20Bananas
21Bananas
Sheet1
Cell Formulas
RangeFormula
B7:B11B7=$A$2
B12:B16B12=$A$3
B17:B21B17=$A$4
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I think I've seen other ways to do similar, but this just came to mind.
Book1
ABCD
1Numbers of rows to repeat5
2Apples
3Pears
4Bananas
5
6Apples
7Apples
8Apples
9Apples
10Apples
11Pears
12Pears
13Pears
14Pears
15Pears
16Bananas
17Bananas
18Bananas
19Bananas
20Bananas
21
Sheet1
Cell Formulas
RangeFormula
B6:B20B6=INDEX($A$2:$A$4,SEQUENCE(ROWS($A$2:$A$4)*$D$1,,1,1/$D$1))
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top