3

I have a list of names:

  1. Alice
  2. Bob
  3. Caleb ...

I want column A in rows 1-10 in Google Spreadsheets to be Alice, 11-20 to be Bob, and so on with the rest of the list.

How can I do this automatically? FLOOR((ROW()-1)/10) gets me the "index" of the name that I want? How do I access it with the names are located in column L? (Alice is L1, Bob is L2, etc). I can copy all the names by hand, but I am wondering if there is a function to do that?

Basically I want:

1. Alice
2. Alice
...
10. Alice
11. Bob
...
21. Caleb
4

Please use the following formula

={TRANSPOSE(SPLIT(REPT(N11&",",4),","));
  TRANSPOSE(SPLIT(REPT(N12&",",4),","));
  TRANSPOSE(SPLIT(REPT(N13&",",4),","))}

enter image description here

(Please adjust ranges and parameters to your needs)

Functions used:

| improve this answer | |
  • 1
    Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Nov 7 at 1:37
  • 2
    Haha good answer ! – Marios Nov 7 at 1:48
  • 2
    Welcome new user @Marios . Simple. Nothing complicated for a new user. :) – marikamitsos Nov 7 at 1:56
0

The following formula will handle the request with any number of names in Column L:

=ArrayFormula(VLOOKUP(SEQUENCE(COUNTA(FILTER(L:L,L:L<>""))*10,1,0),{SEQUENCE(COUNTA(FILTER(L:L,L:L<>"")),1,0,10),FILTER(L:L,L:L<>"")},2,TRUE))

There is no need to alter the formula as more names are added. And it does not matter in which row you place the formula (as long as it isn't also in Column L, of course).

HOW IT WORKS:

Clearly, it is an array formula, which means it will process an entire range.

The first SEQUENCE forms a virtual sequence of numbers whose number of rows is the count of names in L:L times 10, number of columns is 1, and starting number is 0 (i.e., it will form a column of numbers from 0 to 29 for a list of 3 names, a column of numbers from 0 to 99 for a list of 10 names, etc.).

VLOOKUP will then look up each of the elements of this first SEQUENCE in another two-column virtual array (the bit between the curly brackets { } in the formula). That virtual array has as its first column another SEQUENCE whose number of rows is the count of names in L:L, number of columns is 1, starting number is 0 and skip-amount is 10 (i.e., 0, 10, 20, etc.). The virtual array has as its second column the list of names in L:L.

0   Alice
10  Bob
20  Caleb

The element in column 2 of this array will be returned.

And since the final argument of VLOOKUP is TRUE, VLOOKUP will return the last match that was less than or equal to the search element. So 0,1,2,3,4,5,6,7,8 and 9 will all return "Alice"; 10,11,12,13,14,15,16,17,18 and 19 will all return "Bob", etc.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.