×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dynamic Unique List
2

Dynamic Unique List

Dynamic Unique List

(OP)
Hi All,
Long time without a question, but I'm working on something new...

So I have used named ranges to provide for source data in dropdown lists. And I like to do them dynamically, so as I add to list data, it appears without having to redefine fixed ranges.

But now I have something a little different. I have a column of data, that has repeating values, that I want to use as my source, but the length of the column is also variable.
Previously I would have created a named range like:

MyRange with the formula:
=OFFSET('Static Variables'!$F$4,0,0, COUNTA('Static Variables'!$F:$F),1)

This works find, but gives me one entry for every row.

I just want the unqiue values out of that same range, and I tried both:

=OFFSET('Static Variables'!$F$4,0,0, COUNTA(UNIQUE('Static Variables'!$F:$F)),1)
and
=UNIQUE(OFFSET('Static Variable'!$F$4,0,0, COUNTA('Static Variables'!$F$F),1)

And neither works.

Is there a way to use the UNIQUE or other function to provide for the unique values while keeping the range dynamic?

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Dynamic Unique List

You may either:
Copy your list, paste it into another (hidden?) sheet and remove duplicates, or
Dive into Excel's Power Query or
Use UNIQUE function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Dynamic Unique List

Scotty,

Isn't UNIQUE() the result you're looking for WITHOUT using OFFSET()?

BTW, if you're looking to define dynamic ranges, why are you still using A1 notation? Why not use a Structured Table?

So I'm using Insert > Tables > Table and naming my Structured Table, tList.

My List
A
B
A
C
 

In the Name Manager, where you want to assign a dynamic named range...

=UNIQUE(tList)


BTW, if you use A1 notation and reference the entire column, UNIQUE will return a ZERO for the unique value of all empty cells, so in my example, you'd see 0, A, B, C.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Dynamic Unique List

(OP)
Hi Skip,
Didn't you retire? :)

I tried this, but it's not working. Any time I put in the data validation for the source for a List, I get an error, saying that "The Source currently evaluates to an error. Do you want to continue?" with Yes/No buttons. If I say yes, then when I click on the dropdown in the cell, there is nothing (not blank, nothing). Whatever content (if any) was in the cell before is still there, and there are no options to pick.

Did I do something wrong?
I have a named range called StandardsRange with it's definition as -UNIQUE(tStandard) where tStanndard is a table I created using the method you showed above, and I put about 6 rows with 2 repeating values in it.

Not sure what's happening.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Dynamic Unique List

You cannot use a Structured Table name in Data > Validation. You must use a Range Name.

Are you using StandardsRange in your Data > Validation?

Yes, I've retired, but I still dabble in Excel & VBA

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Dynamic Unique List

Maybe this will help....

Right or wrong, what I did was: Start Excel, Insert - Table



OK
That gave me this:



so, I created my list of names:



So far, so good.
Then I selected Table1 and typed over it tList



Like this



Then in cell C1 I typed: =UNIQUE(tList)



And if you want to have it sorted:



Skip, is that correct ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Dynamic Unique List

Andy, yes, although the =UNIQUE(tList) is not on the sheet, but in the Names Manager.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Dynamic Unique List

Quote (Skip)

You cannot use a Structured Table name in Data > Validation.
Not directly, but table name + column name can be converted to range for DV: =INDIRECT("tSourceTable[Field_1]")

combo

RE: Dynamic Unique List

(OP)
Thanks Skip and Andy. I got this working!
Cheers,
-S

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Dynamic Unique List

Plz let us know the solution that worked.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Dynamic Unique List

(OP)
It was a combination, but also I had some misunderstanding it its implementation. It doesn't "Auto update" like a dynamic range might, you have to click the "Refresh" on the table, with the dynamic range on the "UNIQUE" in the set next to the table for it to work. (Usually I would just "add" an item to a dynamic range, but tables don't update the same way, you have to "refresh" them to build them.

It's a little problematic for my users skillset, but I think I can work through them with it.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close