Semicolon delimited list

Hi Everyone,

I am a basic user of Excel and need some help with a semicolon delimited list.

My data set is similar to this:

(a) 1;2;5
(b) 1;3;4
(c) 2;3;5

I understand how to covert this into columns, but what I need is a little more complex than that. Each value represents a yes/no option. I need the data broken down into 5 columns and for the values to be put into their respective columns. For example person a would have a value for columns 1,2,5 (but not the other columns), person b for columns 1,3,4 and person c for columns 2,3,5.

Please help, this is a large dataset and I need an easy way to sort it!

Thanks very much for the suggestion - also I hope I have posted this in the appropriate thread.

I have attached an example using a cut down version of my real data. I would be really grateful if someone could help automate the process, Ihave more than 2000 cases and a bunch of different variables that need to be broken down into groups.

Thanks for that Marvin and Vladimir, it is a huge step to solving my problem.

I was wondering how to modify the formula to include more groups? Sorry if this seems basic, I appreciate the help. As you can see in the example, some of the numbers go up to 7 (which means the 7th group) ot (other) is always the last group when it is included - I have other variables which have between 5 and 17 groups, so it would be great if one of you could explain this to me so I can apply this to the different variables.

Excellent! Problem solved

thanks very much

Hi again everyone,

Just having another little issue with the formula:

In some cases I have more than 10 groups. Unfortunately, if I there is a "12" in the lookup column, this is recognised as belonging to groups 1, 2, and 12. I have attached another example - I am not sure how to make the formula look up an exact match rather than just containing the numeral in the header.

Thanks!

It would help if I did know how to enter the array formula - as a beginner I have no idea and my google searches have not been particularly enlightening except to tell me to press cnt +sht+enter

If you could walk me through it that would be great

Thank you both very much! These were fantastic solutions