Return unique values but with blank cells between values (Google Sheets)

[FKalinx]

Hi there

I am using Google Sheets and want to return a set of unique values but with blank cells between the returned values.

For instance:

Column A
- list of values

Apple
Banana
Apple
Pear
Grape
Apple
Pear



Column B
- list of unique values with blank rows between

Apple



Banana



Pear




Grape

---


I've tried UNIQUE with SPLIT but it doesn't work.

Any help would be much appreciated.

Thanks

 

[Jimmypop]

Hi there

Found this in a google search.. maybe try it...

=FLATTEN(SORT({UNIQUE({A1:A10,IF(A1:A10="","","")})}))

 

[FKalinx]

Hi there

Found this in a google search.. maybe try it...

=FLATTEN(SORT({UNIQUE({A1:A10,IF(A1:A10="","","")})}))

View attachment 74435

You're a star, thanks Jimmypop. It works How would I add 5 blanks between the values? I've tried adding more '"","",""

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Return unique values but with blank cells between values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[FKalinx]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Return unique values but with blank cells between values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks, noted and apologies - I didn't realise that Mr Excel and Excelforum were related.

 

[Fluff]

We are not related, but most sites have a similar rule about cross-posting.

 

[Jimmypop]

You're a star, thanks Jimmypop. It works How would I add 5 blanks between the values? I've tried adding more '"","",""

and if you try...

=TRANSPOSE(Split(join(" , , ,",FLATTEN(SORT({UNIQUE({A1:A10,IF(A1:A10="","","")})}))),","))

 

[FKalinx]

and if you try...

=TRANSPOSE(Split(join(" , , ,",FLATTEN(SORT({UNIQUE({A1:A10,IF(A1:A10="","","")})}))),","))

Thank you so much!

 

[Jimmypop]

Pleasure and thanks for the feedback... the formula I gave can be shortened but at the moment it was what I could come up with...

UPDATE... A bit shorter...

=TRANSPOSE(Split(join(" , , , , , ,",FLATTEN({UNIQUE({A1:A10})})),","))

 

[FKalinx]

Pleasure and thanks for the feedback... the formula I gave can be shortened but at the moment it was what I could come up with...

UPDATE... A bit shorter...

=TRANSPOSE(Split(join(" , , , , , ,",FLATTEN({UNIQUE({A1:A10})})),","))

Brilliant, thank you

If I wanted to add more blank cells, is there an easy way to adjust the formula, say for 10 blanks?