Unique list from multiple columns

[killar65]

Good evening all,
I am a teacher at a small school and have been asked to collate some test results for some other teachers. The students will complete 4 tests over the year, but during that time, new students may be enrolled.
What i would like to do is create a list of unique names in one column E generated from the names contained in columns A - D.
I have searched the internet, but haven't been able to find a solution that works
Is it possible to create this unique list?
I have attached a file to help.
Many thanks in advance,

James

 

[Somendra Misra]

Hi James,

Welcome to Chandoo Forum.

See the attached file, I had make a helper column for bringing all the names in one column first, this can be done using copy n paste. Than I had used Array formula to extract unique names.

Regards,

 

[bosco_yip]

Another option, try this without helper,

In E2, array formula (pressing SHIFT+CTRL+ENTER) copy down :

=INDIRECT(TEXT(MIN(IF((COUNTIF(E$1:E1,A$2:D$12)=0)*(A$2:D$12>0),ROW($2:$12)/1%+{1,2,3,4},4^8)),"R0C00"),)&""

Regards
Bosco

 

[killar65]

Thank you Somendra and Bosco for your prompt replies to my question.
Both worked perfectly, but Bosco's response was more compatible to what i was seeking.
Thank you both very much for taking the time to help me with my problem.