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 f...
Formulas and Functions
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