Unique data list covering multiple columns.

Hi, I hoping someone could help me,
I am trying to create a unique list from 5 columns, I need it to only list each name once and remove any blanks cells, I have found a formula online which works exactly the way i need it to.

=IFERROR(IFERROR(IFERROR(INDEX(Fighters!$H$2:$H$250, MATCH(0, COUNTIF($AD$5:AD5, Fighters!$H$2:$H$250)+(Fighters!$H$2:$H$250=""), 0)), INDEX(Fighters!$I$2:$I$250, MATCH(0, COUNTIF($AD$5:AD5, Fighters!$I$2:$I$250)+($B$2:$B$7=""), 0))), INDEX(Fighters!$J$2:$J$250, MATCH(0, COUNTIF($AD$5:AD5, Fighters!$J$2:$J$250)+(Fighters!$J$2:$J$250=""), 0))), "")

I am able to adjusted the references to and get this to work in my worksheet perfectly BUT what I need to know is how to add additional columns to the formula as you can see this is created for 3 columns. The main part of this that I have names down each row and each name can appear in any of the 5 columns.



Thanks

Attached is one approach to this kind of issue -- easy enough to adapt for range, but it is Volatile given use of INDIRECT... you can use a helper cell to remove the volatililty (or duplicate the INDEX calls) if needed.

In the example the data range is in A2:F11, with first formula in H2, copied down, to return unique entries (of any type)

Formula:

Please Login or Register  to view this content.

Select the range of cells, or ensure that the active cell is in a table.
On the Data tab, click Remove Duplicates (in the Data Tools group).
Do one or more of the following: ...
Click OK, and a message will appear to indicate how many duplicate values were removed

Tell me if this works for you.