Unique list without helpcolumn

[Mile]

I have question which have been driving me mad lately. I know it has to be hard to solve it, but I need to know if it´s possible (or not).

I have in a column a list of values with duplicates. Is it possible to create a dynamic unique list with formulas from this list without using a helpcolumn marking up the unique values?

 

[Domski]

You can use Advanced Filter.

The settings are:

Action - Copy to another location
List Range - Your list of values
Criteria Range - leave blank
Copy to - a cell in a blank column
Unique records only - check this box

Dom

 

[Mile]

Ok, but have to do it with formulas so it´s dynamic - when you update a value in the column .... then the unique list updates.

 

[Aladin Akyurek]

Ok, but have to do it with formulas so it´s dynamic - when you update a value in the column .... then the unique list updates.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=right x:num x:fmla='=SUM(IF(FREQUENCY(IF(A2:A9<>"",MATCH("~"&A2:A9&"",A2:A9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),1))' x:arrayrange="B1">5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>CAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS($B$2:B2)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B2))),"")' x:arrayrange="B2">CAD</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>SAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS($B$2:B3)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B3))),"")' x:arrayrange="B3">SAD</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS($B$2:B4)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B4))),"")' x:arrayrange="B4">FAD</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>SAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS($B$2:B5)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B5))),"")' x:arrayrange="B5">SAF</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF(ROWS($B$2:B6)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B6))),"")' x:arrayrange="B6">KAG</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>SAF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS($B$2:B7)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B7))),"")' x:arrayrange="B7"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>KAG</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS($B$2:B8)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B8))),"")' x:arrayrange="B8"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:str="" x:fmla='=IF(ROWS($B$2:B9)<=$B$1,INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),(ROW($A$2:$A$9)-ROW($A$2)+1)),(ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS($B$2:B9))),"")' x:arrayrange="B9"> </TD></TR></TBODY></TABLE>

A2:A9 on Sheet1 houses a sample.

Define first Rvec (via Insert|Name|Define) as referring to:

=ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1

Then...

B1:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A2:A9<>"",MATCH("~"&A2:A9&"",A2:A9&"",0)),Rvec),1))

B2:

Control+shift+enter, not just enter...

=IF(ROWS($B$2:B2)<=$B$1,INDEX($A$2:$A$9,
   SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",
    MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0)),Rvec),Rvec),
     ROWS($B$2:B2))),"")

and copy down.

 

[Mile]

That was great!!!

 

[Mile]

Are there any hints in understanding how this work?

MATCH("~"&$A$2:$A$9&"",$A$2:$A$9&"",0))?
How does this work for a start? I know how MATCH normally is used, but how does this work?

How does the named formula work?

Other hints?

Thanks again!