Loop with a formula on a dymanic range

[BC2019]

I have a need to loop through a dynamic range with a formula preferrable. If i have to use a macro i can, but would rather not if possible just for ease of maintenance down the road.

My situation is this:

"Names" would have a list of names repeated a number of times (variable but is based on the number of items in "Numbers" and can be obtained with a count. I've done this with no problem already using =INDEX(Names!$B$2:$B$400,INT((ROWS($A$7:A11)-1)/COUNTIF(Final$C$2:$C$1000,11000)+1)). The number of items in "Numbers" can vary but if it changes in count the number of repetitions of names in "Names" column also has to change (in this example im using 3 random numbers which means each person is replicated 3 times). So in essence all items in "Numbers" needs to be repeated for all of the names in "Names" and both need to be variable from time to time as other things in the workbook change. I've looked through various forumns with no luck on how to replicate "numbers" for the items in "Names". Any help would be much appreciated.

Names	Numbers		Names	Numbers
Bobby	Random1		Bobby	Random1
Jill	Random2		Bobby	Random2
Randy	Random3		Bobby	Random3
			Jill	Random1
			Jill	Random2
			Jill	Random3
			Randy	Random1
			Randy	Random2
			Randy	Random3

<tbody>
</tbody>

 

[Eric W]

Welcome to the Board!

I'm not entirely sure of your requirements, but maybe:

	A	B	C	D	E	F
1	Names	Numbers		Names	Numbers
2	Bobby	Random1		Bobby	Random1
3	Jill	Random2		Bobby	Random2
4	Randy	Random3		Bobby	Random3
5		r4		Bobby	r4
6				Jill	Random1
7				Jill	Random2
8				Jill	Random3
9				Jill	r4
10				Randy	Random1
11				Randy	Random2
12				Randy	Random3
13				Randy	r4
14

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =IF(ROWS($D$2:$D2)>COUNTA($A$2:$A$100)*COUNTA($B$2:$B$50),"",INDEX($A$2:$A$100,INT((ROWS($D$2:$D2)-1)/COUNTA($B$2:$B$100))+1)) E2 =IF(D2="","",INDEX($B$2:$B$50,MOD(ROWS($D$2:$D2)-1,COUNTA($B$2:$B$50))+1)) <tbody> </tbody>

<tbody>
</tbody>

Let us know if this is what you're looking for.

 

[BC2019]

I cant get that to work for me. Its just repeating the last item in the "numbers" range over and over again. Not sure what I'm doing wrong. Thanks for the help Eric W.

The formula converted to my sheet looks like this
=IF(A7="","",INDEX('Labor Only'!$A$2:$A$18,MOD(ROWS($A$7:$A40)-1,COUNTA('Labor Only'!$A$2:$A$18))+1))

 

[Eric W]

What cell does that formula go in?

 

[BC2019]

What cell does that formula go in?

Goes in the E1 cell in the example below. The intent is to duplicate the "numbers" the same number of times as the people are duplicated. If would be the same as using a macro to copy/paste "N" number of times, but of course a formula to do that would look much different.

 

[Eric W]

If the A and B columns are on a sheet named 'Labor Only', and you don't want the headers in D1:E1, then the formulas would look like:

	D	E	F
1	Bobby	Random1
2	Bobby	Random2
3	Bobby	Random3
4	Bobby	r4
5	Jill	Random1
6	Jill	Random2
7	Jill	Random3
8	Jill	r4
9	Randy	Random1
10	Randy	Random2
11	Randy	Random3
12	Randy	r4
13

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =IF(ROWS($D$1:$D1)>COUNTA('Labor Only'!$A$2:$A$18)*COUNTA('Labor Only'!$B$2:$B$20), "",INDEX('Labor Only'!$A$2:$A$18,INT((ROWS($D$1:$D1)-1)/COUNTA('Labor Only'!$B$2:$B$20))+1)) E1 =IF(D1="","",INDEX('Labor Only'!$B$2:$B$20,MOD(ROWS($E$1:$E1)-1,COUNTA('Labor Only'!$B$2:$B$20))+1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[BC2019]

If the A and B columns are on a sheet named 'Labor Only', and you don't want the headers in D1:E1, then the formulas would look like:

	D	E	F
1	Bobby	Random1
2	Bobby	Random2
3	Bobby	Random3
4	Bobby	r4
5	Jill	Random1
6	Jill	Random2
7	Jill	Random3
8	Jill	r4
9	Randy	Random1
10	Randy	Random2
11	Randy	Random3
12	Randy	r4
13

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =IF(ROWS($D$1:$D1)>COUNTA('Labor Only'!$A$2:$A$18)*COUNTA('Labor Only'!$B$2:$B$20), "",INDEX('Labor Only'!$A$2:$A$18,INT((ROWS($D$1:$D1)-1)/COUNTA('Labor Only'!$B$2:$B$20))+1)) E1 =IF(D1="","",INDEX('Labor Only'!$B$2:$B$20,MOD(ROWS($E$1:$E1)-1,COUNTA('Labor Only'!$B$2:$B$20))+1)) <tbody> </tbody>

<tbody>
</tbody>

Thanks for taking the time to help Eric W. This worked perfectly for me.

 

[Eric W]

You're welcome.