Convert/count comma separated values into columns with boolean values

[Jannem]

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

[protonLeah]

B2:

=ISNUMBER(SEARCH(Table3[[#Headers],[1]],Table3[[Source]:[Source]]))*1

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

[TMS]

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.

=TRANSPOSE(IF(IFERROR(MATCH("*"&SEQUENCE(11)&"*",TEXTSPLIT(A2,,{".",","}),0),0)>0,1,0))

copied down.

Cancel that assertion. Same problem with 1 and 11

[TMS]

Ok, this copes with 1 and 11.

=TRANSPOSE(IF(IFERROR(MATCH("* "&SEQUENCE(11)&" *"," "&TEXTSPLIT(A2,,{".",","})&" ",0),0)>0,1,0))

[Jannem]

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.

=TRANSPOSE(IF(IFERROR(MATCH("* "&SEQUENCE(11)&" *";" "&TEXTSPLIT(Sheet1!$A2;".";",")&" ";0);0)>0;1;0))

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!

[Jannem]

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!

[AliGW]

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.

[AliGW]

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)

[AliGW]

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)

[TMS]

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.

[shukla.ankur281190]

Try

B2

=ISNUMBER(MATCH(B$1,INDEX(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))-1)*LEN($A2)+1,LEN($A2))),0),0))*1

[AliGW]

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.

[kvsrinivasamurthy]

IN B2 copied to full range

=IF(ISNUMBER(FIND(","&Table3[[#Headers],[1]]&",",","&SUBSTITUTE($A2,".",",")&",")),1,0)

[Jannem]

IN B2 copied to full range

=IF(ISNUMBER(FIND(","&Table3[[#Headers],[1]]&",",","&SUBSTITUTE($A2,".",",")&",")),1,0)

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).