using Dynamic Range and Unique values only


G

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?
 
Ad

Advertisements

H

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,"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top