How to combine columns into a single one

Copper Contributor



I want to use the new "Unique" function on multiple columns and get the list of unique values in a single column, is there a way to do that using the new dynamic arrays?




Edit: Added example file. 

There are 2 lists, list A and list B. My goal is to create another list which consists of the unique values of list A and list B. In the example file I achieved this by simply using the Unique function for list A and list B seperatly in column E and then use the unique function again for the range in column E in column G.

The problem is that this method breaks if I increase the number of unique items in list A, as the cell for the dynamic array is already occupied.


So my question is there a way to do this with the new dynamic arrays that is not dependant on the number of entries and unique items?

8 Replies


If I can recommend you, add a file (without sensitive data) and describe your plan on it. This means that you can be helped more quickly and the best possible solution tailored to your needs can be offered. So everyone is helped.

Help to be helped.


* It's always good to add your operating system and Excel version.


As far as I know, here is some information that might help you.


UNIQUE function


Thank you for your understanding and patience

I know that I don't know (Socrates)

Additional Info: UNIQUE function I would be happy to know if I could help. Nikolino I know I don't know anything (Socrates)



Thanks for the reply. I added a file and some more text in the opening post as per your advice.

I don't really know my way around Office 365 ... I'm still studying.
Here is a page that may help you.

* But I am sure that one of the experts here will take care of your request now that you have a file and suggest the appropriate solution for you.

Thank you for your understanding and patience

I know that I don't know (Socrates)


That could be like


concrete size you may calculate by COUNTA()

@Sergei Baklan That works! Thanks a lot.

@Swengineer , you are welcome

Today you taught me something too. Great technique with UNIQUE (spills) and SEQUENCE. Thank you.