• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Individual values in the list under data validation

N

nazmul_muneer

Member
Dear Forum members,

My data in the sheet1


Dept Product

------ ---------

ESL 100 Watt

ESL 50 Watt

ESL 30 Watt

ESL 23 Watt

ESL 17 Watt

ESL 13 Watt

ESL 07 Watt

Oil 1

Oil 3

Oil 5

SPT Gasket

SPT Spark Plug

SPT Valve

SPT Nut #1234

SPT Nut #5678

SPT Bolt#1234

SPT Bolt#4567

I want to store only unique value of the column Dept in the list under data validation
 
Hi Muneer,


please visit the following site:

http://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formula/


Thanks,

Suresh Kumar S
 
Hi,


Please download the below file.


http://www.2shared.com/document/kxiF_0tp/unique.html


(I Guess this will help you)


Thanks,

Suresh Kumar S
 
Hi Muneer,


Sorry to trouble you can you please upload the sample file and your requirements. (2shared)


Thanks,

Suresh Kumar S
 
Assume in the columnA, the values are

ESL

ESL

ESL

OIL

OIL

SPT

SPT

SPT

I want to load only unique value of the column A (ESL,OIL,SPT)in the list under data validation.


--Muneer
 
Hi, nazmul_muneer!

Given a look at this?

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Regards!
 
@SirJB7

I have seen the Luke M's solution. Actually i want to load individual values i.e. ESL,OIL,SPT in the list from data validation.

Actually i want to know what formula i will have to write in

Data Validation>Allow>Custom>Formula.


I don't want to get any extra helping column


Regards,

--Muneer
 
Hi, nazmul_muneer!

I'm afraid that it isn't possible without helper columns and the technique described in the related article.

Regards!
 
I think SirJB7 is correct.


I've not seen an Excel formula displaying result as "array" [as will be necessary in your case {ESL;OIL;SPT}].


Even in Array formulas we ultimately use MAX, MIN, LARGE, SMALL to get one result out of the resultant ARRAY.
 
Hi, nazmul_muneer!

Yesterday I stepped over this, if I were you I'd give a try:

http://chandoo.org/forums/topic/how-to-create-unique-list-based-on-data-available-in-range-of-cells#post-23816

As far as I could see just at a glance, it doesn't work if there's any empty cell on range, so check it out carefully.

Regards!
 
You must log in or register to reply here.
Back
Top