Forum Discussion

dsingh2255's avatar
dsingh2255
Brass Contributor
Sep 23, 2022

how to make multiple columns data into one unique coloumn Dynamically

Hi All,

I want to make unique name in one column from multiple columns and it should be dynamic

and also formula should be excel version upto 2016.not from excel 365

 

before that i was using a formula

=INDIRECT(TEXT(MIN(IF(($A$2:$D$100<>"")*(COUNTIF($E$1:E1,$A$2:$D$100)=0),ROW($2:$100)*100+COLUMN($A:$D),7^8)),"R0C00"),)&""

 

but when im dragging this formula to 5000 rows the excel file getting hang and slow

 

so i want data in unique column with dynamically.

 

Formulas and Functions

4 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 23, 2022

    dsingh2255 

    I think PowerQuery is your best option with the version of Excel you're running. Without access to UNIQUE, ctrl+shift+enter arrays are going to be a big time calculation crunch.

     

    Please see attached PowerQuery sample.

    • dsingh2255's avatar
      dsingh2255
      Brass Contributor
      Sep 23, 2022
      i can't use power query or excel 365. is there any other way to make dynamic unique column in excel 2016
    • mtarler's avatar
      mtarler
      Silver Contributor
      Sep 23, 2022

      I agree but is PowerQuery in that version? was it called something else back then?

      I think the big lag may be because of using INDIRECT so alternatively you can try this:

      =IFERROR(INDEX(
  FILTERXML("<a><b>"&CONCAT(
    IF(
     ($A$2:$D$100<>"")*(COUNTIF($F$1:F1,$A$2:$D$100)=0),
     INDEX($A:$D,ROW($2:$100),COLUMN($A:$D))&"</b><b>",
     "")
   )&"</b></a>","//b")
 ,1),"")
&""




Resources