Sep 23 2022 06:59 AM
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.
Sep 23 2022 07:41 AM - edited Sep 23 2022 07:41 AM
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.
Sep 23 2022 08:25 AM - edited Sep 23 2022 08:30 AM
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),"")
&""
Sep 23 2022 08:35 AM
Sep 23 2022 08:53 AM
I think @mtarler 's solution is your best option.