Loop with a formula on a dymanic range

BC2019

New Member
Joined
Mar 25, 2019
Messages
4
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.

NamesNumbersNamesNumbers
Bobby
Random1BobbyRandom1
JillRandom2BobbyRandom2
RandyRandom3BobbyRandom3
JillRandom1
JillRandom2
JillRandom3
RandyRandom1
RandyRandom2
RandyRandom3

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,234
Welcome to the Board!

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

ABCDEF
1NamesNumbersNamesNumbers
2BobbyRandom1BobbyRandom1
3JillRandom2BobbyRandom2
4RandyRandom3BobbyRandom3
5r4Bobbyr4
6JillRandom1
7JillRandom2
8JillRandom3
9Jillr4
10RandyRandom1
11RandyRandom2
12RandyRandom3
13Randyr4
14

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
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

New Member
Joined
Mar 25, 2019
Messages
4
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))
 

BC2019

New Member
Joined
Mar 25, 2019
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,234
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:


DEF
1BobbyRandom1
2BobbyRandom2
3BobbyRandom3
4Bobbyr4
5JillRandom1
6JillRandom2
7JillRandom3
8Jillr4
9RandyRandom1
10RandyRandom2
11RandyRandom3
12Randyr4
13

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Mar 25, 2019
Messages
4
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:


DEF
1BobbyRandom1
2BobbyRandom2
3BobbyRandom3
4Bobbyr4
5JillRandom1
6JillRandom2
7JillRandom3
8Jillr4
9RandyRandom1
10RandyRandom2
11RandyRandom3
12Randyr4
13

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,235
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top