How to make name ranges

[Svmaxcel]

I have a list with emp id, name and manager.
I want to use data validation using Combo box
I will getting duplicates using comobox for managers
Is there any way to convert the list into a proper format for excel to make name ranges easie
File attached

 

[bosco_yip]

Try,

1] In E4, copied across until blank

=IFERROR(INDEX($C$5:$C$19,MATCH(0,INDEX(COUNTIF($D$4:D$4,$C$5:$C$19),0),0)),"")

2] In E5, copied across and down :

=IFERROR(INDEX($B$5:$B$19,MMULT(SMALL(($C$5:$C$19<>E$4)/1%%+ROW($B$5:$B$19)-ROW($B$4),ROWS($1:1)),1)),"")

Regards
Bosco

 

[Peter Bartholomew]

A different strategy. I aim to remove every direct cell reference and to replace relative referencing by arrays where possible.
Group leaders (managers) are given by
= INDEX( Manager, SMALL( IF( MATCH(Manager, Manager, 0 )=k, k ), {1,2,3,4} ) )
Group members are
= IFERROR( INDEX( Name, SMALL( INDEX( IF( Manager=GroupLeader, k ), 0, {1,2,3,4} ), k ) ), "" )
If there were many more groups writing the array constant {1,2,3,4} would become an issue and would need to be replaced by a helper range or a formula. The innermost index is simply to prevent SMALL aggregating over the entire 2-dimensional table.
Now I need to look at @Bosco's solution to find out what /1%% is all about

 

[Svmaxcel]

Dear Team,

Appreciate all your valuable support
i am sharing thee original file with dummy data to summarize my query
File Attached

the file is very complicated and i am not able to find a solution as its very difficult


Workbook has 5 sheets
Main is the main page
Raw contains employee performance data
Working contains targets and scores
piv is a pivot of Raw
Submission is the place where all data will be updated

I have created Piv sheet to create ranges for Combo box, but didnt work

How it works
every week, I get data for employees performance, which i fill in Raw data.
the file is then forwarded to Manager, where they discuss the same with employees

on Main sheet, there is a button to select employee
So the Manager will click on the button to select agent name
Agent name is userform to search for emp
1 option is to search using filters and other is to search using typing employee names

There is a radio button to select the method

option 1 : Search by filters.
Manager will click on radio button and select Search by filters.
At this point, they can select the manager name from Combo box and then select the employee name, then click on Select button
(("Manager and employee names are filtered via Raw sheet Column E"))

As soon as they click on select, the information is passed down to H16 and all employee related performance is displayed using Index match

option 2 : Search by typing
This is an easy option, just type few initials of employee and it gets displayed using listbox.

I used below code for searching using typing

Private Sub TextBox3_Change()
Dim i As Long
Me.TextBox3.Text = StrConv(Me.TextBox3.Text, vbProperCase)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("D:D"))
a = Len(Me.TextBox3.Text)
If Left(Sheet2.Cells(i, 4).Value, a) = Left(Me.TextBox3.Text, a) Then
Me.ListBox1.AddItem Sheet2.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet2.Cells(i, 5).Value
End If
Next i
End Sub

and used this code to useform initialize for combobox

Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = Range("Manager")
End Sub

I am unable to use the Combobox, please help