unique dynamic list from table of data with duplicates

I've seen several examples, but still unable to create a unique dynamic list based from a column of data in an excel file.  I expect it to use offset and index, match, small functions.  No VBA please.  Helper column okay if needed.
dynamic-Range-help-needed.xlsx
LVL 1
jvantassel1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
Does Excel's built in find duplicates feature not do what you want?

Excel toolbar Remove Duplicates
0
jvantassel1Author Commented:
No, it should automatically update the range.  I pull the data down and the range changes.  Using the remove duplicates on the ribbon is fine for a one time solution, but not for a dynamic range.
0
Saurabh Singh TeotiaCommented:
Check Column-K..It does what you are looking for..It's an array formula need to be entered by ctrl+shift+enter..

Saurabh...
dynamic-Range-help-needed.xlsx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

[ fanpages ]IT Services ConsultantCommented:
Hi jvantassel1,

Further to Saurabh Singh Teotia's reply, here is how I would propose to do this without the use of a Table (just for the sake of completeness if anybody is searching for something similar in the future).

If the dynamic range you mentioned going to be on column [F]?

For example, within the attached workbook I have added [rngCTYPE_DESC] that RefersTo:
=OFFSET(Data!$F$6,0,0,COUNTA(Data!$F$6:$F$3000))

This will count the number of cells that are not empty in a range [F6:F3000], & provide a range that starts at [F6] & stretches down column [F] to the extent of the data.  The last row (3000) can be adjusted to suit your needs.

However, to meet your requirements with the question...

I have added a column [L] to the worksheet [Data] as a "Helper" column.

Column [ L ] has a named range, [BlanksRange], defined within it: [=Data!$L$6:$L$58].
Column [ J ] has a named range, [NoBlanksRange], defined as [=Data!$J$6:$J$58].

[BlanksRange] extends down the rows (from 6) to 58; the last row where data exists in column [ F ].
[NoBlanksRange] extends down to the same last row.

Cell [L6] contains a formula: =IF(COUNTIF($F$6:F6,F6)=1,F6,"")

This is copied from Cell [L6] down to Cell [L58].

The range [J6:J58] has the array formula:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

(This is documented on Chip Pearson's site: [ http://www.cpearson.com/excel/NoBlanks.aspx ]).


I presume the use of a "dynamic range" is to influence the extent of the rows (presently 58).

Please could you confirm this is your intention?
Q_28720880.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvantassel1Author Commented:
Yes, I want a dynamic range because I don't know the number of items that will be in the range.  Most of the examples I saw used the offset function in the formula for the range as you showed above.  Also, by using named tables the number of rows is defined by the table, so shouldn't need to use an arbitrary number of cells i.e., [F6:F3000].  I haven't seen a lot of examples with the use of tables.

Did this answer your question?
0
jvantassel1Author Commented:
I like Saurabh's solution, however I would like to "stuff it" into a range, then just call the range instead.
0
Saurabh Singh TeotiaCommented:
You can change the same to range and it will do what you are looking for...

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.