1

Hello, Gentlemen!

 

Here is the situation: I need to get a list of distinct/unique values in a column using a formula on Smartsheet. I know how to create this formula on excel, but when i convert to Smartsheet, it simply doesn't work.

 

The formula that I used in Excel is the following:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

 

The formula converted to Smartsheet is the following:

=IFERROR(INDEX($[Column6]$2:$[Column6]$9, MATCH(0, INDEX(COUNTIF($[Column7]$1:[Column7]1, $[Column6]$2:$[Column6]$9), 0, 0), 0)), "")

 

Could you guys help me with this one? The name just repeats, I have tried inumerous things, but i don't know what it is.

Functionality

Comments

Hi Cesar,

SmartSheet is not Excel, but I did it. It´s not pretty - but seems to work.

The screen shot is attached and here are the formulars:

[Spalte2]1=Name1

[Spalte3]1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JOIN([Spalte2]:[Spalte2]; "#"); "##"; "#"); "##"; "#"); "##"; "#")

[Spalte4]1=LEFT([Spalte3]1; FIND("#"; [Spalte3]1) - 1)

[Spalte2]2=IF(Name2 <> [Spalte2]1; Name2)

[Spalte3]2=SUBSTITUTE(MID([Spalte3]1; LEN([Spalte4]1) + 2; 9999); "##"; "#")

[Spalte4]2=IF(FIND("#"; [Spalte3]2) > 0; LEFT([Spalte3]2; FIND("#"; [Spalte3]2) - 1))

[Spalte2]3=IF(COUNTIF([Spalte2]$1:[Spalte2]2; =Name3) = 0; Name3)

[Spalte3]3=SUBSTITUTE(MID([Spalte3]2; LEN([Spalte4]2) + 2; 9999); "##"; "#")

[Spalte4]3=IF(FIND("#"; [Spalte3]3) > 0; LEFT([Spalte3]3; FIND("#"; [Spalte3]3) - 1))



Enter your names in [Name] and pull down [Spalte2]3 [Spalte3]3 [Spalte4]3

Kind regards

Andreas

PS: Sent an invitation to this sheet.