Hi everyone. I've been racking my brain with this one for a while now and am hoping someone can help me to understand what change I need to make to return unique values. I have a workbook with two worksheets. One worksheet contains a list of every employee in my local organization, and in the next column has the supervisor name for each employee (looks like this):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee 1[/TD]
[TD]Supervisor 1 [/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Supervisor 1
[/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]Supervisor 2[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]Supervisor 2[/TD]
[/TR]
[TR]
[TD]Employee 5[/TD]
[TD]Supervisor 3[/TD]
[/TR]
</tbody>[/TABLE]
This continues for up to 2000 rows.
On the other worksheet, I am trying to pull the employee list for each supervisor (to be used as a drop down later). My desired outcome would display the employee name for each matching supervisor (where the supervisor's name is provided in Row 1). It would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor 1[/TD]
[TD]Supervisor 2[/TD]
[TD]Supervisor 3[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 3[/TD]
[TD]Employee 5[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Employee 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've started with an index to match the supervisor name with the employee name, however, I can not for the life of me figure out how to use the subarray to only return unique values. Here's my formula that I'm entering into cells A2:C3 for the desired outcome above:
{=INDEX(TCS!$A$2:$A$2000,MATCH(A$1,TCS!$B$2:$B$2000,0))}
The formula works to pull an employee's name that matches the above supervisor name, however, it does not continue beyond the first employee that matches. Instead, I get a repeat of Employee 1 over and over again, instead of it finding the next employee that matches the Supervisor name. I know there should be a CountIF somewhere in here to make this work, but can't figure it out.
Any help would be appreciated
Thanks!
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee 1[/TD]
[TD]Supervisor 1 [/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Supervisor 1
[/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]Supervisor 2[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]Supervisor 2[/TD]
[/TR]
[TR]
[TD]Employee 5[/TD]
[TD]Supervisor 3[/TD]
[/TR]
</tbody>[/TABLE]
This continues for up to 2000 rows.
On the other worksheet, I am trying to pull the employee list for each supervisor (to be used as a drop down later). My desired outcome would display the employee name for each matching supervisor (where the supervisor's name is provided in Row 1). It would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor 1[/TD]
[TD]Supervisor 2[/TD]
[TD]Supervisor 3[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 3[/TD]
[TD]Employee 5[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Employee 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've started with an index to match the supervisor name with the employee name, however, I can not for the life of me figure out how to use the subarray to only return unique values. Here's my formula that I'm entering into cells A2:C3 for the desired outcome above:
{=INDEX(TCS!$A$2:$A$2000,MATCH(A$1,TCS!$B$2:$B$2000,0))}
The formula works to pull an employee's name that matches the above supervisor name, however, it does not continue beyond the first employee that matches. Instead, I get a repeat of Employee 1 over and over again, instead of it finding the next employee that matches the Supervisor name. I know there should be a CountIF somewhere in here to make this work, but can't figure it out.
Any help would be appreciated
Thanks!
