Listing Question

[superbeast326]

I have a list of eight names.

Is there a formula where I can make the first name repeat from a1:a6, second a7:a12, so on and so forth.

 

[Jon von der Heyden]

Lets say your 8 names are in column F, starting in row 1.

In A1, and then copied down:

=INDEX(F:F,INT((ROW()-1)/6)+1,1)

 

[alocke]

So you want 8 names in total. Name1 in rows A1:A6, name2 in rows A7:A12 etc. etc.
Or do you want to set it up as an array index so A1 has name1, A2 has name2 etc. Until A7 which will have name1 again?

 

[paddydive]

Hey Jon von der Heyden

That was great.... appreciate if you could explain how it works

 

[Jon von der Heyden]

We use INDEX to return the relevant name. So INDEX(Names,1,1) will return the 1st name from a range name called Names. The trick is to produce repeating row index numbers (the highlighted argument), in this case each has to be repeated 6 times.

ROW() will return the row number that the formula is entered into (see formula A2).

Deduct 1 from this so that the array becomes zero-based (see formula B2).

n = 6, so divide the result by 6 (see formula C2). The first 6 items will be 0.?, the next 6 1.?, the next 2.? and so on...

We are eventually looking for a row index number, which is always an integer, so we truncate the result (see formula D2).

Finally we add 1 so that it becomes one-based again (see formula E2). Voila we have a row index that repeats itself 6 times...

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #95B3D7;;">Row</td><td style="background-color: #95B3D7;;">Row-1</td><td style="background-color: #95B3D7;;">(Row-1)/6</td><td style="background-color: #95B3D7;;">Int((Row-1)/6)</td><td style="background-color: #95B3D7;;">Int((Row-1)/6)+1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.1666667</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.3333333</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.6666667</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.8333333</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1.1666667</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">1.3333333</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1.6666667</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1.8333333</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A2</th><td style="text-align:left">=ROW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=A2-1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=B2/6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=INT(<font color="Blue">C2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=D2+1</td></tr></tbody></table></td></tr></table><br />

 

[superbeast326]

So you want 8 names in total. Name1 in rows A1:A6, name2 in rows A7:A12 etc. etc.
Or do you want to set it up as an array index so A1 has name1, A2 has name2 etc. Until A7 which will have name1 again?

The first one.

 

[superbeast326]

We use INDEX to return the relevant name. So INDEX(Names,1,1) will return the 1st name from a range name called Names. The trick is to produce repeating row index numbers (the highlighted argument), in this case each has to be repeated 6 times.

ROW() will return the row number that the formula is entered into (see formula A2).

Deduct 1 from this so that the array becomes zero-based (see formula B2).

n = 6, so divide the result by 6 (see formula C2). The first 6 items will be 0.?, the next 6 1.?, the next 2.? and so on...

We are eventually looking for a row index number, which is always an integer, so we truncate the result (see formula D2).

Finally we add 1 so that it becomes one-based again (see formula E2). Voila we have a row index that repeats itself 6 times...

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="background-color: #95B3D7;;">Row</td><td style="background-color: #95B3D7;;">Row-1</td><td style="background-color: #95B3D7;;">(Row-1)/6</td><td style="background-color: #95B3D7;;">Int((Row-1)/6)</td><td style="background-color: #95B3D7;;">Int((Row-1)/6)+1</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.1666667</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.3333333</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.6666667</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.8333333</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1.1666667</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">1.3333333</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1.6666667</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1.8333333</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">A2</th><td style="text-align:left">=ROW()</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B2</th><td style="text-align:left">=A2-1</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">C2</th><td style="text-align:left">=B2/6</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D2</th><td style="text-align:left">=INT(C2)</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E2</th><td style="text-align:left">=D2+1</td></tr></tbody></table></td></tr></tbody></table>

and hence, use it in a VLOOKUP Function. Am I right?

 

[superbeast326]

How will you go about doing it in the reverse order?

 

[Jon von der Heyden]

and hence, use it in a VLOOKUP Function. Am I right?

No, with INDEX. Did you try the combined formula in my initial reply?

What do you mean reverse order? Do you want the return the last name, then second last, etc? I suppose I could puzzle it out but can't you rather just sort the names?

 

[superbeast326]

I have already sorted the names in the descending order according to the persons weight, I want to keep it like that.