using Dynamic Range and Unique values only

[GregDAngelo]

I'm using dynamic ranges on my spreadsheet which for the most part is
working fine. the one problem i've run into is having a dynamic range
that has repeating values. i've read other posts and seen some
solutions but they require either using advanced filter or an extra
column neither of which suits my needs or desires.

here's what i have (at least the main Data as there are other columns)
Category Layout
M10101 BLUES01A
M10101 CELTIC01A
M10101 CLASSIC01A
M10102 JAZZ02A
M10102 KIDS01A
M10201 WORLD11A
M10201 WORLD12A
M10201 WORLD09A
M10301 TVDVDS01T
M10301 VALUE01A

what i want is to use a dynamic range on the category column but have
the reference return *ONLY* unique values since i want to be able to
add categories later without modifying anything. Any ideas?

 

[Herbert Seidenberg]

Using a simplified list, assume your data looks like this:
list_g list_h
A 2 A 2
A 2
D 6 D 6
D 8 D 8
B 5 B 5
A 9 A 9
D 8
A 2

D 8
B 5
.... ....

and you want to list the unique data in list_g and list_h
but you don't want to use Filter or extra helper columns.
List_g and list_h are dynamic range names.
Blanks are allowed and the lists are not sorted.
Define these other names:
row_g Refers To
=ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(list_g)))
list_ghv Refers To
=list_g&list_h
m_ghv Refers To
=MATCH(list_ghv,list_ghv,0)
rpt_ghv Refers To
=(row_g=m_ghv)*NOT(ISBLANK(list_g))
Into the adjacent same size columns enter the respective array formulas
with Cntrl+Shift+Enter
=IF(rpt_ghv=1,list_g,"")
=IF(rpt_ghv=1,list_h,"")