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

  • dsingh2255's avatar
    dsingh2255
    Brass Contributor
    Nov 17, 2022
    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's avatar
        dsingh2255
        Brass Contributor
        Nov 17, 2022
        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.

Resources