How to combine columns into a single one

%3CLINGO-SUB%20id%3D%22lingo-sub-1752834%22%20slang%3D%22en-US%22%3EHow%20to%20combine%20columns%20into%20a%20single%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752834%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20use%20the%20new%20%22Unique%22%20function%20on%20multiple%20columns%20and%20get%20the%20list%20of%20unique%20values%20in%20a%20single%20column%2C%20is%20there%20a%20way%20to%20do%20that%20using%20the%20new%20dynamic%20arrays%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1752834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752983%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20combine%20columns%20into%20a%20single%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752983%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823070%22%20target%3D%22_blank%22%3E%40Swengineer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20and%20describe%20your%20plan%20on%20it.%20This%20means%20that%20you%20can%20be%20helped%20more%20quickly%20and%20the%20best%20possible%20solution%20tailored%20to%20your%20needs%20can%20be%20offered.%20So%20everyone%20is%20helped.%3C%2FP%3E%3CP%3EHelp%20to%20be%20helped.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20It's%20always%20good%20to%20add%20your%20operating%20system%20and%20Excel%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAs%20far%20as%20I%20know%2C%20here%20is%20some%20information%20that%20might%20help%20you.%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH1%20id%3D%22toc-hId-556944886%22%20id%3D%22toc-hId-556944887%22%20id%3D%22toc-hId-556944887%22%20id%3D%22toc-hId-556944887%22%20id%3D%22toc-hId-556944887%22%3EUNIQUE%20function%3C%2FH1%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Funique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Funique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20that%20I%20don't%20know%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752990%22%20slang%3D%22de-DE%22%3ERE%3A%20How%20to%20combine%20columns%20into%20a%20single%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752990%22%20slang%3D%22de-DE%22%3EAdditional%20Info%3A%20UNIQUE%20function%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Funique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Funique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%20I%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%20Nikolino%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1753189%22%20slang%3D%22en-US%22%3EBetreff%3A%20How%20to%20combine%20columns%20into%20a%20single%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%20I%20added%20a%20file%20and%20some%20more%20text%20in%20the%20opening%20post%20as%20per%20your%20advice.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello

 

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?

 

Thanks!

 

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?

7 Replies
Highlighted

@Swengineer 

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

https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

 


Thank you for your understanding and patience

Nikolino
I know that I don't know (Socrates)

Highlighted
Additional Info: UNIQUE function https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e?ui=e... I would be happy to know if I could help. Nikolino I know I don't know anything (Socrates)
Highlighted

@Nikolino 

 

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

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

https://www.myonlinetraininghub.com/excel-functions/excel-unique-function

* 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

Nikolino
I know that I don't know (Socrates)
Highlighted

@Swengineer 

That could be like

=SORT(UNIQUE(INDEX($A$2:$D$19,MOD(SEQUENCE(2*18)-1,18)+1,INT(SEQUENCE(2*18,,18)/18))))

concrete size you may calculate by COUNTA()

Highlighted

@Sergei Baklan That works! Thanks a lot.

Highlighted

@Swengineer , you are welcome