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

	A​
1​	Names​
2​	Anthony​
3​	Bob​
4​	Mary​
5​	Mike​
6​	Robert​
7​	Anthony​
8​	Robert​
9​	Bob​
10​	Mary​
11​	Mary​

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!