Convert/count comma separated values into columns with boolean values

Hi all,

I am trying to save the following problem: a surveyor has supplied me with a dataset that contains a column representing the return of a set of checkboxes in a survey. So, for example, in an option-set containing 11 options, the checkboxes are numbered 1 to 11. For every checked box its value is added to a single cell, comma separated (e.g. 1,5,6,9,10). My challenge is to extract the values from the commas separated list, using a formula and convert them into boolean values. So, with 11 options in my example, it would result in 11 columns containing boolean values (1=checked, 0 unchecked). I explored using IF, COUNTIF/COUNTIFS and SUBSTITUTE but I run into a few problems:
- only checked options return a result, so I can't use the positions as a means to identify the values;
- not all cells have multiple values, so there is not always a comma;
- adding wildcards doesn't seem to work. I end up with false positives for column 1, as values 10 and 11 are also identified;
- I would like a future proof solution, as I cannot be certain that the checkbox results are ordered (ascending/descending).

I have made an example worksheet to illustrate the problem. I am sorry if this would seem basic stuff to some of you, but I am stuck. Many thanks in advance for giving it your thought and attention.


Jan-Willem

B2:

Please Login or Register  to view this content.

edit: I just noticed that it doesn't work with 1 vs 11, I'll look at it later...

This works but NOT output to a Structured Table. Some of your expected results are wrong, and you have some full stops instead of commas.

Formula:

Please Login or Register  to view this content.

copied down.

Cancel that assertion. Same problem with 1 and 11

Ok, this copes with 1 and 11.

Formula:

Please Login or Register  to view this content.

Hi TMS,

Many thanks for your advice and effort. I had to alter the formulas slightly, as my version of Excel separates the arguments within the function with semicolons instead of comma's.

Formula:

Please Login or Register  to view this content.

Also had to manage the fact that the formula 'spills' over to next columns (didn't anticipate that). So, converted table back to a range, made room for the function to spill over. Now it all works perfectly, also in the actual worksheet. So many thanks again!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Hi Trevor,

Your solution is perfectly usable in a standard range, but it doesn't seem to work in a table.

Excel-help confirms this: "Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range."

I would prefer your fully automated/dynamic formula, but as this doesn't work in a table. Do you have any pointers how the formula would look like if it was coded per column (representing the 'checkbox'-ID). Thanks again for your consideration!

Try this:

=IFERROR(IF(FIND(" "&Table3[[#Headers],[1]]&" ",SUBSTITUTE(SUBSTITUTE(" "&[@Source]&" ",","," "),"."," ")),1),0)

=ALS.FOUT(ALS(VIND.ALLES(" "&Table3[[#Kopteksten];[1]]&" ";SUBSTITUEREN(SUBSTITUEREN(" "&[@Source]&" ";",";" ");".";" "));1);0)

Correction. This copied across and down:

=IFERROR(IF(FIND(" "&Table3[[#Headers],[1]]&" ",SUBSTITUTE(SUBSTITUTE(" "&Table3[@[Source]:[Source]]&" ",","," "),"."," ")),1),0)

=ALS.FOUT(ALS(VIND.ALLES(" "&Table3[[#Kopteksten];[1]]&" ";SUBSTITUEREN(SUBSTITUEREN(" "&Table3[@[Source]:[Source]]&" ";",";" ");".";" "));1);0)

You're welcome. Thanks for the rep.

Your solution is perfectly usable in a standard range, but it doesn't seem to work in a table.

I did say this in my first post

This works but NOT output to a Structured Table.

But Ali's solution works well.

Try

B2

Formula:

Please Login or Register  to view this content.

Thanks for the rep. Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

IN B2 copied to full range

Formula:

Please Login or Register  to view this content.

Originally Posted by kvsrinivasamurthy

IN B2 copied to full range

Formula:

Please Login or Register  to view this content.

Thanks. This works too, however the solution by AliGW is better for me, as it is more tolerant of different formatting of the source cells (either using periods, comma's or spaces as a delimiter).