# Repeating unique data from a column in Google Sheets

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

A solution as a single formula:

``````=transpose(
split(
concatenate(
arrayformula(
split(
rept(unique(A:A) & "\|", 3),
"|"))), "\"))
``````
• Would you mind explaining the breakdown of the formula so I can understand what is happening? 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. May 11 '17 at 7:37

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.