how to make multiple columns data into one unique coloumn Dynamically

Contributor

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.

 

4 Replies

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

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),"")
&""




i can't use power query or excel 365. is there any other way to make dynamic unique column in excel 2016

@dsingh2255 

I think @mtarler 's solution is your best option.