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

Brass Contributor

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

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

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

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

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

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

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

@dsingh2255 Use PowerQuery. See attached,

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

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.

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

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