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]

try;

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

 

[superbeast326]

Thanks, I tried it and it worked, can you please kindly explain to me the INDEX function.

 

[Jon von der Heyden]

In this context, INDEX is merely used to return a reference from a grid according to a row coordinate and a column coordinate.

So assume you have A1:Z10

If the row coord is 1 and the column coord is 1 then this will return A1.

If the row coord is 2 and the column coord is 3 then this will return C2.

I hope this makes sense.

 

[superbeast326]

One more question, please take the time to answer it.

Is there a formula that I can use to repeat 1,2,3,4,5,6 eight times down another column?

 

[Jon von der Heyden]

To repeat 1;2;3;4;5;6, in A1 copied down:

=INT(MOD((ROW()-1)/6,6)+1)

how many times it repeats depends on how far you copy it down...

 

[superbeast326]

What I meant was to repeat this pattern:
A1=1, A2=2, A3=3, A4=4, A5=5, A6=6

 

[Jon von der Heyden]

Sorry, I copied the wrong formula.

=MOD(ROW()-7,6)+1

 

[superbeast326]

Sorry, forgot to thank you there. So here, Thank You.

 

[superbeast326]

Just one more quick question, is it possible to concatenate all the odd numbered names into one text and then concatenate all the even numbered names into another text?

 

[Jon von der Heyden]

Not without vba or external addins.