Retrieve unique items array

[Legacy 143009]

Hi,

There are formulas already to extract unique items from a range but all of them are based/depends on comparing the list column reference by COUNTIF.

Is there anyway to retrieve a unique array alone, for example may work in TEXTJOIN function?


E.g. TEXTJOIN(", ",0,UNIQUEFUNCTION)

Thanks a lot!

 

[Peter_SSs]

Suggesting TEXTJOIN indicates that you may want all the unique items listed in a single cell. Is that your requirement? Or are you looking for ways to list them down a column?
Is the original list in separate cells a column?

 

[Legacy 143009]

As I mentioned in my post, listing to a seperate column depends on the listed column reference.

TEXTJOIN is not important. It was just an example. Theoretically I want to have {"uniqueItem1", "uniqueItem2", "uniqueItem3"} array in the end.

 

[Marcelo Branco]

Not sure what you want

See if this example helps

Data

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Names​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Anthony​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mary​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mike​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Robert​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Anthony​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Robert​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Bob​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Mary​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Mary​

[/td][/tr]
[/table]

Paste this formula in the Formula Bar and press F9
=IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),ROW(A2:A11)-ROW(A2)+1),A2:A11,"")

You see...
={"Anthony";"Bob";"Mary";"Mike";"Robert";"";"";"";"";"";""}

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(FREQUENCY(IF(1-(range=""),MATCH(range,range,0)),ROW(range)-ROW(INDEX(range,1,1))+1),range)

would deliver an array of unique items from range along with a bunch of FALSE values. This can be processed by feeding to relevant functions.

 

[Legacy 143009]

@Marcelo Branco, your solution is what I need. Thank you very much. It also produces empty array elements but not a big deal. Also it doesn't require Ctrl+Shift+Enter.
TEXTJOIN with ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert. (What I need!)
TEXTJOIN without ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert, , , , , , .
In fact your actual array is: {"Anthony", "Bob", "Mary", "Mike", "Robert", "", "", "", "", ""}
Thanks a lot again!

@Aladin Akyurek your solution is a bit tricky. It is also an array formula.
TEXTJOIN with ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE.Also the same result in TEXTJOIN without ignoring empty values.

Both thanks a lot for your great effert!

 

[Aladin Akyurek]

@Marcelo Branco, your solution is what I need. Thank you very much. It also produces empty array elements but not a big deal. Also it doesn't require Ctrl+Shift+Enter.
TEXTJOIN with ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert. (What I need!)
TEXTJOIN without ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert, , , , , , .
In fact your actual array is: {"Anthony", "Bob", "Mary", "Mike", "Robert", "", "", "", "", ""}
Thanks a lot again!

@Aladin Akyurek your solution is a bit tricky. It is also an array formula.
TEXTJOIN with ignoring empty values, the result is: Anthony, Bob, Mary, Mike, Robert, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE.Also the same result in TEXTJOIN without ignoring empty values.

Both thanks a lot for your great effert!

It's the same thing... Additionally, it doesn't include an empty/blank cell as an unique item.

 

[Legacy 143009]

It's the same thing... Additionally, it doesn't include an empty/blank cell as an unique item.

After thinking for a while, I think you are right. Omitting empty/blank cells thumbs up
Also returning pure FALSE values will be absolutely beneficial to evaluate them in other functions.

You are very very right Sir!