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



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


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:


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


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