Extracting unique values from a list in multiple columns into a single column (using formula)

[shakehands]

hi guys,

first of all, many thanks for your help.
I've been searching for the answer to my problem, but I can't seem to find one anywhere, especially inside my head. So I thought, maybe the excel masters here can give me some solution !

So here is the trouble :
I have 5 columns of data, which is sometimes unique, and sometimes has duplicate value.
Here is the example :

On Column A (Listed from row 1 (A1) to row 5 (A5) > I have a,b,c,d,f
On Column B (Listed from row 1 (B1) to row 5 (B5) > I have c,d,a,f,g
On Column C (Listed from row 1 (C1) to row 5 (C5) > I have a,c,b,f,g
On Column D (Listed from row 1 (D1) to row 5 (D5) > I have d,g,b,c,a
On Column E (Listed from row 1 (E1) to row 5 (E5) > I have a,c,d,e,f

This is the illustration :

---A---B--C--D---E
1| A | C | A | D | A
2| B | D | C | G | C
3| C | A | B | B | D
4| D | F | F | C | E
5| F | G | G | A | F

----------------
All I want is to list the result on column F, and here is how the result should be :

---F
1| A
2| B
3| C
4| D
5| E
6| F
7| G

Note : It doesn't have to be alphabetically ordered.
As long as it lists each of all the items in the previous 5 columns, that is totally awesome !

Thanks a lot guys!

 

[mukun]

Hey Aladin,

I cant remember what happened but I managed to find a 3 column answer - for finding unique values from every 8th column in the range $A$2:$IE$82:

Cell IG2 =IFERROR(INDEX($A$2:$IE$82,MOD(ROW(A1)-1,81)+1,INT((ROW(A1)-1)/81)*8+1),$A$2)
Cell IH2 =IF(COUNTIF($IG$2:IG2,IG2)=1,ROW(),"")
Cell II2 =IFERROR(INDEX(IG:IG,MATCH(LARGE(IH:IH,COUNTIF(IH:IH,">0")-ROW(A1)+1),IH:IH)),"")

This can be catered for any amount of columns, but thing is that the range needs to start from A2.

THis is not my work. Trying to find the person who assisted me with this.