## [Solved] Formula to return unique values from a column

Discuss the spreadsheet application

### [Solved] Formula to return unique values from a column

Hello,

I'm looking for a way to return a list of all the unique values from one column into another column.

So column A contains =
EGGS
EGGS
SAUSAGE
EGGS
BACON
BACON

Therefore column B returns =
EGGS
SAUSAGE
BACON

I've found a number of guides in Excel which show methods on how to do this (my preferred option being here- https://www.mrexcel.com/forum/excel-questions/460780-array-formula-remove-duplicates-list.html - specifically post #8. I have this working in Excel but it doesn't seem to translate into OpenOffice.

Does anyone have any advice for how I can achieve this on OpenOffice Calc? It needs to be a formula, I can't use a filter.

Thanks and regards,
Last edited by zivilyn99 on Tue Aug 15, 2017 5:52 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
zivilyn99

Posts: 4
Joined: Tue Jul 25, 2017 9:00 pm

### Re: Formula to return unique values from a column

What about Data - Filter - Standard Filter - More - No duplication?

(About formula what you are looking - see here)
Attachments
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English

JohnSUN-Pensioner
Volunteer

Posts: 762
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

### Re: Formula to return unique values from a column

zivilyn99 wrote:I'm looking for a way to return a list of all the unique values from one column into another column. … Does anyone have any advice for how I can achieve this on OpenOffice Calc? It needs to be a formula, I can't use a filter.
I created this sample using the link in the previous post.
201708141411.ods
(7.96 KiB) Downloaded 1009 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3959
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: [Solved] Formula to return unique values from a column

All - thankyou for your inputs.

MrProgrammer - Many thanks for the example sheet. This satisfies my requirements perfectly.

Thanks and regards,
OpenOffice 4.1.3 on Windows 10
zivilyn99

Posts: 4
Joined: Tue Jul 25, 2017 9:00 pm

Return to Calc

### Who is online

Users browsing this forum: Google [Bot], Villeroy and 30 guests