help with combo boxes and duplicate entrys

[kevindict]

Hi,

I'm attempting to automate a report given out to employees for th
company I work for. The data for this report is sent from ou
corporate office daily. All Data is on worksheets there are no form
involved. What I'm attempting to do is create a combo box with a lis
of employee names that are generated from this report. The problem i
the employee names are often different,occur at a different startin
point, and have mltiple entrys. The column that I would like to tak
the data from looks something like This:

SUPERVISOR NAM
Mary Smith
Mary Smith
Mary Smith
Mary Smith

EMPLOYEE NAM
John Doe
John Doe
John Doe
Adam Jones
Adam Jones
Alex Keaton
Alex Keaton
Alex Keaton
Alex Keaton

What I would like to do is create two extra columns at the beginning o
the report. The first is an index number to be used with a vlooku
function to display the name instead of the index number. The secon
would be a list of names, with each name listed below the cell tha
contains 'Employee Name" only once. Could someone please help me ou
with this? I'm at my wit's end.

Thank You,
Kevi

 

[Max]

Maybe you'd like to experiment with this set-up ..
(.. creates a Data Validation list with unique items)

Assuming this data is in col C, in C1:C16

SUPERVISOR NAME
Mary Smith
Mary Smith
Mary Smith
Mary Smith

EMPLOYEE NAME
John Doe
John Doe
John Doe
Adam Jones
Adam Jones
Alex Keaton
Alex Keaton
Alex Keaton
Alex Keaton

Put in D1: =IF(OR(C1="",COUNTIF($C$1:C1,C1)>1),"",C1)
Put in E1: =IF(D1="","",ROW())

Select D1:E1 and copy down by a safe "max"
# of rows for the data expected in col C, say to E100

Put in B1:
=IF(ISERROR(MATCH(SMALL(E:E,ROW(A1)),E:E,0)),"",OFFSET($D$1,MATCH(SMALL(E:E,
ROW(A1)),E:E,0)-1,))

Copy down to B100

You should get in B1:B6
(for the sample data in col C)

SUPERVISOR NAME
Mary Smith
EMPLOYEE NAME
John Doe
Adam Jones
Alex Keaton

Create a dynamic range "MyList"
for the data extracted in col B:

Click Insert > Name > Define
In box for "Names in workbook:", enter: MyList
In box for "Refers to:", put:
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT(--(Sheet1!$B$1:$B$100<>"")),)
Click OK

Now let's create data validation for col A:

Select col A
Click Data > Validation
Under "Allow:", select: List
Put in box for "Source:" : =MyList
Click OK

Click on any cell in col A, and you'll get
a droplist to select items from the dynamic range "MyList"

Note: You could also create the DV list in another sheet,
using the same steps as outlined above
--
And if you want a combo box instead,
draw one from the forms toolbar on any sheet
Right-click on the combo box > Format Control
Put in the box for "Input range:" : MyList
Select a cell-link, e.g.: $H$1
Click OK

 

[Max]

Just an add-on clarification:

If you want the DV to show only the "names",
and if the only non-name phrases which'll appear in col C are
SUPERVISOR NAME and EMPLOYEE NAME,

just amend the formula in D1 from

Put in D1: =IF(OR(C1="",COUNTIF($C$1:C1,C1)>1),"",C1)

to

Put in D1:
=IF(OR(C1="",C1="SUPERVISOR NAME",C1="EMPLOYEE
NAME",COUNTIF($C$1:C1,C1)>1),"",C1)