HOW TO MAKE UNIQUE LIST FROM MULTIPLE COLUMNS WITH EXCEL 2016 FORMULA

[dsingh2255]

Hi All,

I want a Unique List from multiple columns into one only with Excel 2016 Version. I have attached dummy file too.

in this file i have data in13 columns A to O and I want all unique list in Column N

if anyone knows the any function in 2016 then pls let me know.... i can't use excel 365 or power query so function should be upto excel 2016 version

pls let me know guys...!!!

[#2]

@dsingh2255 Add two zero's where the current formula refers to 100 and increase the 7^8 factor to 7^10.

=INDIRECT(TEXT(MIN(IF(($A$2:$M$10000<>"")*(COUNTIF($N$1:N1,$A$2:$M$10000)=0),ROW($2:$10000)*10000+COLUMN($A:$M),7^10)),"R0C0000"),)&""

Tested it and it works, though it slows down the calculations significantly. 

[#3]

yes its too slow even its hanging the sheet...is there any other way to do that so that it can't get hang?

[#4]

@dsingh2255 Use PowerQuery. See attached,

[#5]

yes i know we can achieve the result by power query or excel 365 but thats the limitation of my software it can support upto 2016 excel version.

[#6]

@dsingh2255 But PQ is integrated in Excel2016. Why not use it?