Validation List Unique Values

[matt4003]

Hi All,

I have a named range called Customers. It dynamically picks up the list of customers. Problem is that I want my List to show each customer only once.

Here is what the table looks like:

Customer
A
A
A
A
A
A
B
B
B
B
B
B
B
C
C
C
C
C
C


I want the list to show only one A, B, C for it's selection options.

Make sense?

Any ideas?

Regards,
Matt

[royUK]

Why have you got duplicates in the list?usually validation is based on a list of unique entries,this can be on a separate sheet

[matt4003]

Hello royUK,

Thank you for the reply.

My data set has duplicate values because for each customer there are many variables. The data set changes all the time, it is not static, or a separate look up tab would be useful.

Here is a better way to look at it:

Customer Variable
A Sales
A FCST
A ACD
A DOI
B Sales
B FCST
B ACD
B DOI
C Sales
C FCST
C ACD
C DOI

I would like the validation list to grab the unique list of the customers....right now it grabs all the customer, so 4 customer A's, 4 customer B's, etc.....

Make sense or should I attach an example work sheet?

Regards
Matt

[shg]

You can do this with formulas or a UDF and dynamic named range.

For a formula-based approach, see http://www.get-digital-help.com/2009...array-formula/.

If you want a UDF, holler.

[matt4003]

Hello shg,

Thanks for the suggestion. I was able to get most of the formula on the "get-digital" web site. Still struggling with part of it. The other problem is that is takes a long time to calculate with the several hundred rows that it needs to calculate.

What is UDF? I don't recognize that term.

Thanks,
Matt

[DonkeyOte]

UDF - User Defined Function (VBA Function)

The demo. illustrated by shg will be relatively expensive performance wise given use of Arrays in conjunction with Volatile Named Range construct (OFFSET)

Using Post # 3 as an example such that A1:B13 contains:

Please Login or Register  to view this content.

another way of creating a (dynamic) unique listing in C would be to do the following:

Please Login or Register  to view this content.

the above tells us is one calc. how many unique customers we have.

What we do next depends rather on whether or not the Customer ID's (Col A) are sorted alphabetically or not ?

All the implications thus far are that, yes, the codes are sorted in which case:

Please Login or Register  to view this content.

You will note that surplus rows are populated with #N/A errors - this is deliberate and is to assist us in creating our Dynamic Named Range (DNR) for use in Validation... we thus create a DNR as follows:

Please Login or Register  to view this content.

We can now use this Name in a given cell as source for DV

Please Login or Register  to view this content.

If your Customer ID's are not sorted alphabetically then things will become a little more expensive formulae wise in terms of generating the unique listings ... when dealing with large volumes of data it is always wise to sort the data appropriately as this generally permits efficiency gains long-term.