Splitting column into multiple columns (of varying size)

Hi all,

I would like to request your help.

I have build a vba code which provides me with a column full of data. The length of this column is a variable, different each time.

In this column, each cell contains either one or multiple names, divided by a comma

Thus, say starting Column "K4:K8" (but length is variable)

AA,BB,CC
AA,CC,
BB
AA,BB,DD
AA

Out of all this information, I would like to list the different names, and paste them somewhere else.

Thus:

AA
BB
CC
DD

I tried splitting the cells, but due to the varying nature, I was not able to make it work.

So my plan was: after splitting, I would have to remove the commas, put all data in a single column and remove duplicates and sort it alphabetically.

Any help would be much appreciated.

Hi & welcome to the board
How about

Please Login or Register  to view this content.

Hi Fluff13,

Thank you for your warm welcome and the help. I will try to implement your formula into my workbook.

If you don't mind me asking, I am new to "CreateObject("scripting.dictionary")".. I just googled it and it seems it acts as some kind of variable in which you can save multiple items?

Edit: I just tried to implement it and it did not completely do the job:

Split Names.PNG

I believe it has to do with moving the L4 part?

Thanks,

Hi MarvinP,

Thank you for your help.

However, I am more or less unable to install add-ins and therefore have to stick to (pure) VBA.

I most certainly appreciate your help, but have to look for an VBA solution to the problem..

Kind regards,

In what way didn't it work?
From what you have posted, that's what I'd expect in that col L has all the unique values from col K.
For more about dictionaries have a look here https://excelmacromastery.com/vba-dictionary/

Hi FLuff13,

I do apologies. I had the impression it had split just K4 into L4:L6, but it had actually done all steps in one sweep..

That is actually amazing, "Dictionary" is a very powerful tool.

You (via "Dictionary") skipped splitting the value in multiple columns, putting them together below each other, removing commas etc..

Thank you very much, I will try to fully understand the function!

Kind regards

You're welcome & thanks for the feedback

Hi Fluff13,

I would like to run through one more thing with you on this:

If I would have cells with a space after the comma, it would give both the name with and without a space before it:

AA, BB, CC
AA
AA, CC
CC
AA, BB
BB

For example would give me:

AA
BB
CC
CC
BB

Which makes for an interesting case. As I am only looking for the ones without a space in front of it. I thought about removing the Spaces in the whole column first, but that would turn all double names into a single string: John Doe would become JohnDoe, which is not ideal.

I could manipulate the input by removing all spaces after the comma? Thus add something like replace(c1, ", " , ",") within the sub?

Looking forward to your view.

Best,

If you make this change

Please Login or Register  to view this content.

It will cater for commas with & without a space afterwards

The trim addition indeed solved the problem!

This forum is very useful for efficient coding, as the "simple" trim is more efficient than rewriting columns etc..

Once again, much appreciated! I added reputation to your post

Glad to help & thanks for the feedback