Forum Discussion

dsingh2255's avatar
dsingh2255
Brass Contributor
Nov 16, 2022

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

 

excel

5 Replies

Sort By
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 17, 2022

    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. 

Resources