Unique Entries

[cookeetree]

G'day Excel Gods,

I have a table showing Training Courses, the Employee who completed it and the date they completed it.

On a separate worksheet, there is a formula that searches through this table to find those employees who've completed a specific course ($B$1).

{=IFERROR(INDEX('Training Completed'!$G:$L,SMALL(IF('Training Completed'!$G:$G=$B$1,ROW('Training Completed'!$G:$G)),ROW(1:1)),5),"")}

However, where an employee has completed a course multiple times, I see their name multiple times.

How can I modify (or rewrite) the formula to show only unique names?

Cheers, Jason.

 

[cookeetree]

Pretty hard to tell from here.

Go to File -> Account and see what is there:
View attachment 20458

Yeah, that's the one.

 

[cookeetree]

Sorry, been off work for a couple of day.

=IFERROR(UNIQUE(INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8)))),"")

I reckon this is the one I need.

The list will be ever-growing, though and is on another worksheet. I know I can modify the cell reference range to suit, but how do I modify the ROW function to take this into account?

 

[Peter_SSs]

I reckon this is the one I need.

I reckon you can do simpler than that ..

Colour here was just for my benefit for checking 'Course 1'

cookeetree 2020-08-18 1.xlsm
	G	H	I	J	K
1	Course				Employee
2	Course 1				Emp 1
3	Course 2				Emp 1
4	Course 1				Emp 4
5	Course 1				Emp 2
6	Course 3				Emp 2
7	Course 3				Emp 1
8	Course 2				Emp 4
9	Course 1				Emp 2
10	Course 2				Emp 2
11	Course 2				Emp 1
12	Course 3				Emp 3
13	Course 2				Emp 4
14	Course 2				Emp 1
15	Course 1				Emp 1
16	Course 1				Emp 1
17	Course 1				Emp 2
18	Course 1				Emp 2
Training Completed

This formula needs to be entered in one cell only. The other results will automatically 'spill' to any other required rows. If you have made the range in the formula big enough (I've used 1000 rows) to any expected expansion of the 'Training Completed' sheet then there will be no need to adjust this formula as it will automatically pick up any new entries in that range.

cookeetree 2020-08-18 1.xlsm
	B
1	Course 1
2	Emp 1
3	Emp 4
4	Emp 2
5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=UNIQUE(FILTER('Training Completed'!K2:K1000,'Training Completed'!G2:G1000=B1,""))
Dynamic array formulas.

 

[cookeetree]

I reckon you can do simpler than that ..

You da man! That's perfect, thanks! The Excel Gods come through yet again.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Please update your profile as described in post 2 so that next time helpers know what version you have. If you did not have Excel 365 then then my formula would not have been applicable for you.
If we had known your version from the start then you could have had your answer 2 or 3 days ago.