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

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

Resources