Populate combo box with unique values only

[sjayar]

Hi All,


I have a worksheet which has a combobox and i am populating with the
sheet2 values(assume column c values).

this works fine.
But........... sheet's coulmn c contains 50 values with repeated values
in it.

I want my combo box to contain unique values only.

_*example:*_
SHEET2 COLUMN C

1. john
2. Harry
3. John
4. Petter
5. Harry
6. Petter
7. petter

 

[Max]

.. I want my combo box to contain unique values only.

Here's one way to set it up ..
(Combo box is assumed drawn from the forms toolbar)

In Sheet2,
Using 2 adjacent empty cols
Put in D1: =IF(C1="","",IF(COUNTIF($C$1:C1,C1)>1,"",ROW()))
Put in E1:
=IF(ISERROR(SMALL(D,ROW())),"",
INDEX(C:C,MATCH(SMALL(D,ROW()),D,0)))
Select D1:E1, copy down to say, E100,
to cover the max expected extent of data in col C

Click Insert > Name > Define, and put in
Names in workbook: MyList
Refers to:
=OFFSET(Sheet2!$E$1,,,SUMPRODUCT(--(Sheet2!$E$1:$E$100<>"")))

Now, put as the input range for the combo box: MyList
The combo box will display only the unique values from col C in Sheet2