Is there an efficient way to chart / segment this data that is segmented by commas?

[misterv]

Is there an efficient way to graph the results of the 2nd column? I have survey results that are separated by commas. This is relatively a smaller sample result. I could do it manually by creating another excel table. However, is there an efficient way to do this for large data sets??

Thanks for your help.

[Richard Buttrey]

HI,

Does the attached help

[misterv]

Hi richard,

Thanks for your help! Are you just using countif function? Can you explain the equation in column I? I understand that it is supposed to find a unique value. However, what are the components? Do you just type that equation in the bar? What does column M and those equations do? Is the purpose of that just to sort? Sorry im a noob. Ah, I have an older version of excel and it shows a strange equation. (_xlfn.UNIQUE(_xlfn._xlws.FILTER(B2:B2906,B2:B2906<>""))

[JeteMc]

As you are using the 2013 version of Excel, Power Query (a free add-in from Microsoft) might be an option.
In the attached file the green/white table on the Table1 sheet is produce using the following Power Query advanced editor code:

Please Login or Register  to view this content.

Note that before using Power Query the data on the Acne Products sheet was converted into a table (Ctrl + t)
Let us know if you have any questions.

[Richard Buttrey]

Sorry,

I realised that you may not have Excel 365 and had made a mental note to be sure to say when replying that you first need to produce a list of unique items starting in column I. For testing I had used the two new functions available in 365 in cell I2.

=UNIQUE(FILTER(B2:B2906,B2:B2906<>""))

I forgot to mention this. :-(

The simplest way to produce the column I list without 365 would be to copy column B and then use Excel's REMOVE DUPLICATES functionality. The L2 formula is another 365 function, in this case

=SORT(I2:J9,2,1) , but of course you won't see that either, just the _xlfn....warning. This automatically sorts I2:I9 using column 2 as the sort key and populates L2:L9. These new functions, there are a few others, are really powerful and helpful.