1

I have a selection of values in Column A, some of which are repeated X (where X varies) times, like this:

Apples  
Apples  
Oranges  
Pears  
Pears
Bananas

I want to end up with a list in another column where each unique value is repeated N times (where in this case N is 3). So the above would look like this:

Apples
Apples
Apples
Oranges
Oranges
Oranges
Pears
Pears
Pears
Bananas
Bananas
Bananas

I have been trying to figure a way of combining =UNIQUE and =REPT to get me there but haven't yet found a way. What am I missing? Is there another way?

Can anyone help?

(Aside - I then want to have the results sorted alphabetically without manually having to Data > Sort the range).

0

A solution as a single formula:

=transpose(
   split(
     concatenate(
       arrayformula(
         split(
           rept(unique(A:A) & "\|", 3),
   "|"))), "\"))
  • You are a God among men. Thank you. – GrapesInASalad May 11 '17 at 7:17
  • Would you mind explaining the breakdown of the formula so I can understand what is happening? – GrapesInASalad May 11 '17 at 7:17
  • Thank you for the kind word, sir. The general idea is that the rept/unique combo gives you all the values necessary. The rest is just massaging them to the required form of a single column. I suggest that you progressively extract the functions from the inside to see what they produce. I.e. first try =rept(unique(A:A) & "\|", 3), then =split( rept(unique(A:A) & "\|", 3), "|") and so on. – ttarchala May 11 '17 at 7:37
1

You might create a pivot table (to extract uniques in alphabetical order) and then in Row1 of your sheet:

 =offset('Pivot Table 1'!A$1,int((row()-1)/3),0)  

copied down to suit.

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.