Generate list of employee names from column

[sparkytech]

My GoogleFu seems to be failing me on this one. I have a list of tasks in column "A" and employee names in column "B". These names repeat down the list. How can I generate a list of all employees in this task list?

 

[kweaver]

Would you provide sample data and result(s) you're looking for to have a better understanding of the issue? Please use XL2BB when posting.
It would also help to know what version of Excel you are running (change your profile accordingly).

Are you trying to see what tasks are associated with each employee? If you have 365, this might do it:

Book4
	A	B	C	D
1	Tasks	Employee	Uniq Emp	Associated Tasks
2	Task1	Washington	Washington	Task1, Task4, Task5, Task11, Task16, Task17
3	Task2	Cleveland	Cleveland	Task2, Task7, Task9, Task10, Task20
4	Task3	Lincoln	Lincoln	Task3, Task8, Task13, Task19
5	Task4	Washington	Jackson	Task6
6	Task5	Washington	Truman	Task12, Task14, Task15, Task18
7	Task6	Jackson
8	Task7	Cleveland
9	Task8	Lincoln
10	Task9	Cleveland
11	Task10	Cleveland
12	Task11	Washington
13	Task12	Truman
14	Task13	Lincoln
15	Task14	Truman
16	Task15	Truman
17	Task16	Washington
18	Task17	Washington
19	Task18	Truman
20	Task19	Lincoln
21	Task20	Cleveland
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(B2:B21)
D2:D6	D2	=TEXTJOIN(", ",TRUE,TRANSPOSE(FILTER(A2:A21,C2=B2:B21)))
Dynamic array formulas.

 

[Fluff]

Along with the sample data already requested, what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sparkytech]

Along with the sample data already requested, what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using Excel 2016, example below:

Book2
	A	B	C	D
1	Task Name	Employee
2	Task 1	Jim		Generated list below
3	Task 2	Steve		Jim
4	Task 3	Robert		Steve
5	Task 4	Joe		Robert
6	Task 5	Robert		Joe
7	Task 6	Jim		Mike
8	Task 7	Steve		Albert
9	Task 8	Mike
10	Task 9	Albert
11	Task 10	Albert
12
Sheet1

 

[kweaver]

What about:

Book4
	A	B	C	D
1	Task Name	Employee
2	Task 1	Jim		Generated list below
3	Task 2	Steve		Jim
4	Task 3	Robert		Steve
5	Task 4	Joe		Robert
6	Task 5	Robert		Joe
7	Task 6	Jim		Mike
8	Task 7	Steve		Albert
9	Task 8	Mike
10	Task 9	Albert
11	Task 10	Albert
Sheet3

Cell Formulas
Range	Formula
D3:D8	D3	=INDEX(B2:B11,MATCH(0,COUNTIF($D$2:D2,B2:B11),0))

 

[sparkytech]

Thanks for the reply, I'm not having much luck. Is there a way i can download your changes to the sheet?

 

[kweaver]

In 2016, that formula probably needs to be an array formula (CTRL+Shift+Enter). See if that's the issue.

 

[Dave Patton]

N.B. You can copy and paste the information shown below to a clean sheet.
Click on the icon below the f(x) in the heading and then move to your spreadsheet and paste.

Unique.xlsm
	A	B	C	D	E
1	Task Name	Employee		Generated list below
2	Task 1	Jim		Jim	Jim
3	Task 2	Steve		Steve	Steve
4	Task 3	Robert		Robert	Robert
5	Task 4	Joe		Joe	Joe
6	Task 5	Robert		Mike	Mike
7	Task 6	Jim		Albert	Albert
8	Task 7	Steve
9	Task 8	Mike
10	Task 9	Albert
11	Task 10	Albert
5b

Cell Formulas
Range	Formula
D2:D7	D2	=INDEX(B2:B11,MATCH(0,COUNTIF($D$1:D1,B2:B11),0))
E2:E7	E2	=IFERROR(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-ROW($E$2)+1)/(ISNA(MATCH($B$2:$B$11,E$1:E1,0))),1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sparkytech]

Still working on this... will report back soon, thanks!

 

[sparkytech]

Still working on this... will report back soon, thanks!

Ok, after a lot of trial and error, I finally settled on this, and it works 99%.

{=IFERROR(INDEX($B$2:$B$11,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$11),0)),"")}

I have 2 small issues remaining. In cell D8, there is a "0". The other thing is not a problem, just a wish. I read somewhere that this list could also be sorted alphabetically using the "small" function. Is that possible?