Unique Entries

cookeetree

New Member
Joined
Mar 2, 2015
Messages
48
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I suggest that you update your Account details (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, & this sounds like such a situation. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What do you have in the 5 column in Range ('Training Completed'!$G:$L,)
 
Upvote 0
The best formula will still depend on your Excel version, which you aren't sharing with us. ;)

I suggest that you update your Account details (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, & this sounds like such a situation. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
I hope @Peter_SSs comes with smaller formula for office version other than 365

Book1
CDEFGHIJKLMNOP
9Course Namea
10CourseNameUsing Office 365Other Version
11aqqq
12awww
13aeee
14arrr
15btkk
16aq 
17aw 
18ak 
19
20
21
22
Sheet1
Cell Formulas
RangeFormula
K11:K16K11=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)))),"")
N11:N18N11=IFERROR(INDEX(INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8))),AGGREGATE(15,6,(FREQUENCY(IFERROR(MATCH(INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8))),INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8))),0),0),IFERROR(MATCH(INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8))),INDEX($H$11:$H$18,SMALL(IF($C$11:$C$18=$E$9,(ROW($11:$18)-ROW($11:$11)+1)),ROW($1:$8))),0),0))^0)*ROW($1:$8),ROWS($N$11:N11))),"")
Dynamic array formulas.
 
Upvote 0
and sorry @Peter_SSs I missed the row function once again

Replace with =IFERROR(INDEX(INDEX($H$12:$H$19,SMALL(IF($C$12:$C$19=$E$10,(ROW($12:$19)-ROW($12:$12)+1)),(ROW($2:$9)-ROW($2:$2)+1))),AGGREGATE(15,6,(FREQUENCY(IFERROR(MATCH(INDEX($H$12:$H$19,SMALL(IF($C$12:$C$19=$E$10,(ROW($12:$19)-ROW($12:$12)+1)),(ROW($2:$9)-ROW($2:$2)+1))),INDEX($H$12:$H$19,SMALL(IF($C$12:$C$19=$E$10,(ROW($12:$19)-ROW($12:$12)+1)),(ROW($2:$9)-ROW($2:$2)+1))),0),0),IFERROR(MATCH(INDEX($H$12:$H$19,SMALL(IF($C$12:$C$19=$E$10,(ROW($12:$19)-ROW($12:$12)+1)),(ROW($2:$9)-ROW($2:$2)+1))),INDEX($H$12:$H$19,SMALL(IF($C$12:$C$19=$E$10,(ROW($12:$19)-ROW($12:$12)+1)),(ROW($2:$9)-ROW($2:$2)+1))),0),0))^0)*(ROW($2:$9)-ROW($2:$2)+1),ROWS($N$12:N12))),"")
 
Upvote 0
I think it's Office 365. Does that sound right?
Pretty hard to tell from here. :LOL:

Go to File -> Account and see what is there:
1597539873792.png
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top