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

Contributor

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...!!!

 

5 Replies

@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. 

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

@dsingh2255 Use PowerQuery. See attached,

 

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.

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