[Solved] Formula to return unique values from a column

Discuss the spreadsheet application

[Solved] Formula to return unique values from a column

Postby zivilyn99 » Mon Aug 14, 2017 5:07 pm

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

Postby JohnSUN-Pensioner » Mon Aug 14, 2017 5:16 pm

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

(About formula what you are looking - see here)
Attachments
UnDouble.png
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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Formula to return unique values from a column

Postby MrProgrammer » Mon Aug 14, 2017 9:14 pm

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 782 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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3810
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby zivilyn99 » Tue Aug 15, 2017 5:54 pm

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: No registered users and 29 guests