Split multiple text in individual cells and count them as separate instances in chart or pivot.

[Jitka]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Reporting period[/TD]
[TD]Condition[/TD]
[TD]Location[/TD]
[TD]Feed[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]June 18[/TD]
[TD]headaches, dental, eye[/TD]
[TD]UK[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]June 17[/TD]
[TD]blood,urinary, dental[/TD]
[TD]UK[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]June 18[/TD]
[TD]brain, blood, urinary, dental[/TD]
[TD]US[/TD]
[TD]Tube[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]May 17[/TD]
[TD]brain, urinary,dental,eye,[/TD]
[TD]US[/TD]
[TD]Tube[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]April 18[/TD]
[TD]eye, dental[/TD]
[TD]Can[/TD]
[TD]Drip[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]April 17[/TD]
[TD]eye, dental[/TD]
[TD]Can[/TD]
[TD]Drip[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]Dec 15[/TD]
[TD]dental, urinary, brain, blood[/TD]
[TD]Can[/TD]
[TD]Drip[/TD]
[/TR]
</tbody>[/TABLE]

Hello

please could someone advise me on this? I have multiple rows for various people, some are on there multiple times split by reporting periods and so on (column for period, location, etc).
One of the columns (called 'condition') has multiple text in exported from database. I need to be able to pull this into a graph or table that would split those conditions and basically make a new column (called by the various different conditions, such as column for dental, column for eye etc).
I could use the countifs functions but this is limited as I also want to be able to drill to reporting period, I literally have around 20 different columns that I would like to use in the analysis and so ifs doesn't seem like an option unless I make lots of variants.

So far, I've tried to split the text into different columns and then manually cut and paste every different condition into a new column and called the columns by the name of the condition. This enabled me to create pivot tables and drill down to whatever level I wanted but it was lots of manual work which is always open to error never mind the time it took to do.

Is there an easy way how I can easily pull this into pivot (or any suggestion of what tool) to break it down by different conditions?
Above table is a made up example of what raw data I have in a very small snapshot.

Does this make sense? I come up this time and time again as I have lots of multiselect text in the cloud database I use and this is how it pulls it into excel (I use 2016).

Please bear with me, if anyone could explain in simple terms I'd be really grateful. I am not a whizz by any means.
Thank you so much
Jitka

 

[Jitka]

Thank you Peter, that is so useful. I will either clean the data first or use your new formula. Kind Regards
Jitka

 

[Peter_SSs]

Thank you Peter, that is so useful. I will either clean the data first or use your new formula. Kind Regards
Jitka

Cheers.

 

[Jitka]

Hi all

I wonder if you could help? I pulled my newly populated data into a pivot table, hoping to count how many clients have each condition. But the Count function is not only counting how many times 'ear' for example is mentioned, it's counting the actual formula too which gives me an incorrect number. Basically it gives me total of all the rows but I only need the total for when the condition is counted. Would you have any tips on how to overcome this please?
thank you

 

[Peter_SSs]

I don't understand the problem you are having. Can you post some sample data that demonstrates the problem and explain again in relation to that sample data?

 

[Jitka]

hi Peter

i don't seem able to send a screenshot. Basically I followed the formula and was able to input the different values in the correct columns.
Now my second step was to calculate how many times each condition was actually affecting the clients.
I created a pivot table, normally the pivot would count the values, but instead of just counting the actual values in each column, i.e. ear etc, it's also including cells that do not have the illness conditions in but still have the formula in that I pasted across that part of the spreadsheet. So it's counting the empty cells too (because they contain the formula).
I also tried to get around this issue by using CountA function on my spreadsheet, again it's counting the empty cells where value is false but it's including the formula. i wish I could paste a snippping image here. I am not sure how to create url for an image.
Does this explain?
Thank you
jitka

 

[Peter_SSs]

Without being able to reproduce the problem, it is difficult to investigate the cause or solution.

 

[Jitka]

I'll explore how I can get the image on here, thanks for your patience