## [Solved] Unique Value Formula (very researched)

### [Solved] Unique Value Formula (very researched)

Hello! I have been researching this for a couple of weeks, reading every forum post I can get my hands on and even downloading users' proffered sheets. I'm as close as I can get and I'm now stuck. I have two formulas I can sort of work with:

I need to have a column of single letters (e.g., A, B, C, B, D, F, B, C, A) pared down to unique values (e.g., A, B, C, D, F). This has to be dynamic and all these work in the background on a separate, hidden sheet. I create new spreadsheets for every new order, so I cannot use the filters, please (not even an option). All of this works fine in Excel but not in OO.

1. =IF(ISNUMBER(ZZ2);OFFSET(\$A\$1;ZZ2;0);"")

In the forum post from 2017 that has a list of "Eggs, Sausage, Bacon", etc., I downloaded the sheet from one of the posts. Please see attached JPG for what's going on.

For some reason, this formula stops working at a certain point, and if there are two of the same letters near each other (???). I can't explain it.

2. =IF(COUNTIF(A\$3:A3;A3)=1;A3;"")

This formula works perfectly except that my column now has blank cells. I can use this formula, but is there a way to pare down the results further so "blank" cells (which aren't blank, they have the formula in them) can be removed? That would solve my problem instantly, I think.

3. I keep reading about databases. Should I just give up and learn to use a database? I don't even know if this will do what I want.

I'm at a loss. Thank you!
Attachments
Last edited by monkeyisland on Thu Jul 11, 2019 10:24 pm, edited 1 time in total.
OpenOffice 4.1.2, Windows 7/10/XP
monkeyisland

Posts: 4
Joined: Tue Jul 02, 2019 1:39 am

### Re: Unique Value Formula (very researched)

Welcome to the Forums.
A database may be a good option in the long run, but the learning curve, for most, is quite steep, especially if you've never worked with a database before.
Getting to your query, I see two columns, one marked Dups, the other marked NoDups.
You have the same letters under both headings.
If the idea is to count the number of times a character occurs in a row of characters, then the formula listed in your point 2 is close... that will return the character if that character occurs only once in that range,
On the other hand, a similar formula for your Dups heading might be [code]=if(countif(A\$3:A3;A3)>1,A3,"")

I could give you better codes if you can provide an anonymized copy of your actual spreadsheet. This may be important as you mention about the range being on a different sheet, yet the ranges in your formulae would refer to the current sheet.

Rereading your post, I think I misunderstood it at first... but getting a copy of your actual spreadsheet, even with anonymized data, would save a lot of back and forth hassle, as I would be able to see just how your data is laid out,
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6277
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Unique Value Formula (very researched)

Hi Russell B,

That's a great idea. I will upload an anonymized copy of my spread sheet at work or if I get a chance tonight I can draw up a replica.

I have been working on single sheets for the testing period just to keep typing simple.

Thanks!
OpenOffice 4.1.2, Windows 7/10/XP
monkeyisland

Posts: 4
Joined: Tue Jul 02, 2019 1:39 am

### Re: Unique Value Formula (very researched)

monkeyisland wrote:I need to have a column of single letters (e.g., A, B, C, B, D, F, B, C, A) pared down to unique values (e.g., A, B, C, D, F). This has to be dynamic …
Review the formulas in my attachment, noting terms defined with Insert → Names → Define. NubTop and Temp both use a relative reference and they will be easier to understand if the active cell is B5.
201907011937.ods

monkeyisland wrote:Please see attached JPG for what's going on.
Picture attachments are a nuisance for us because it forces the volunteers to create a spreadsheet for testing. In the future, unless you are reporting a display issue, please attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

monkeyisland wrote:In the forum post from 2017 that has a list of "Eggs, Sausage, Bacon", etc., I downloaded the sheet from one of the posts.

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.

[Tutorial] Ten concepts that every Calc user should know
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: 3863
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Unique Value Formula (very researched)

Hello!

Ok I've attached a copy of the form I'm trying to build.

Sheet 1, Column D12 to D36 is a list of 1-25 items, each with an alphanumeric name (a number and a code letter). My ultimate goal is to have only code letters (without repeating) listed near the top right in the Codes box. What I was able to do successfully in Excel was, e.g.:

1C - Steller
2B - ipsum
3C - lorum

Would appear as "C, B" <----I was clever enough to have a comma appear if there was a following code, but not appear for the last code

With OpenOffice, I've since learned it's a bit different and so this is what I'm hoping to achieve again.

Sheet 2 has most of the "behind the scenes" work happening. I have a column A for the codes (works great), Column B for uniques (but leaves "blank" cells that I can't figure out how to get rid of).

Please know that I am a NOOB on every level so I have to break it down in this manner to understand and follow each step of my process. I'm sure one massive formula would take care of all of this in a heartbeat

MrProgrammer, sorry my previous post wasn't up to par with a few things; I actually appreciate that you wanted links and not JPGs, so in the future I'll keep that in mind!

I've downloaded the attached sheet you offered (above) and I"m running into the exact same issues I did with your code from the 2017 post. If I were to replace column A (SET) information with every letter of the alphabet (26 cells, no repeats), I would have expected all 26 letters to be repeated in column B (Nub), but it doesn't, and coding is not something I understand easily, so I can't figure it out. It's the same issue I was having with the 2017 formula I downloaded (also yours) where it works fine for 5 or 6 cells, but then stops working altogether. I'm sure it's me; I just don't get it.

Any help would be appreciated! This is a side- / enjoyment project that WOULD help me, but it's been a few months in the making (I'm slow) and there's no rush on this.
Last edited by monkeyisland on Thu Jul 11, 2019 10:26 pm, edited 1 time in total.
OpenOffice 4.1.2, Windows 7/10/XP
monkeyisland

Posts: 4
Joined: Tue Jul 02, 2019 1:39 am

### Re: Unique Value Formula (very researched)

Any thoughts?

Just trying to either:
1. Get unique values from a list that needs to be dynamic
or maybe (as a possible workaround)
2. How to concatenate cells but ignore blank value cells (the blank can be replaced with anything, like "-" for example).
OpenOffice 4.1.2, Windows 7/10/XP
monkeyisland

Posts: 4
Joined: Tue Jul 02, 2019 1:39 am