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).

Improve this question

2 Answers 2

Reset to default
0

A solution as a single formula:

=transpose(
   split(
     concatenate(
       arrayformula(
         split(
           rept(unique(A:A) & "\|", 3),
   "|"))), "\"))
Improve this answer
2
  • Would you mind explaining the breakdown of the formula so I can understand what is happening?
    – GrapesInASalad
    May 11, 2017 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, 2017 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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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