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

 

[nikio8]

wow, Muhammad, you are using my idea, just slightly different.
not even using a test is all keywords were used up.

 

[Muhammad_Usman]

wow, Muhammad, you are using my idea, just slightly different.
not even using a test is all keywords were used up.

Yes Bro in excel you can do same thing with different methods....And i think its easy to use just formula instead using codes...

 

[nikio8]

Yes Bro in excel you can do same thing with different methods....And i think its easy to use just formula instead using codes...

i did not know that isnumber() will not care that search is error. you learn something every day.

how do you use those excel formats here? it would be quite handy to be able to paste spreadsheet grid not just text .

 

[Muhammad_Usman]

i did not know that isnumber() will not care that search is error. you learn something every day.

how do you use those excel formats here? it would be quite handy to be able to paste spreadsheet grid not just text .

Check this thread https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
Download MrExcel HTML Maker (updated 2017) and enjoy format.

 

[Jitka]

Hello thank you so much. Apologies for delayed response I have been offline and still on holiday without pc access. I'll try it out in practice I'll be back end of August.
Wow you are amazing, can't thank k you enough for all your time. Speak soon. Jitka

 

[Jitka]

Hello

I am back from my holiday. Just read all the responses, thank you so much for all your time and advice. I will slowly work through it. Thank you. Have a great day. Jitka

 

[Peter_SSs]

I would be a little wary of using the ISNUMBER(SEARCH formulas as suggested in posts 9 and 10. The problem is demonstrated here where the formula reports 'ear' when 'ear' is not one of the "words" listed in column C.

Excel Workbook
	C	D	E	F	G	H	I	J	K
1	Condition	Location	Feed		eye	ear	dental	urinary	forearm
2	dental, eye, forearm	UK	Nil		eye	ear	dental		forearm
Extract to column 1

However, that formula can be made more robust as follows. Formula copied across and down.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N
1	Condition	Location	Feed		eye	ear	dental	urinary	forearm	headaches	blood	brain
2	dental, eye, forearm	UK	Nil		eye		dental		forearm
3	headaches, dental, eye	UK	Nil		eye		dental			headaches
4	blood,urinary, dental	UK	Nil				dental				blood
5	brain, blood, urinary, dental	US	Tube				dental	urinary			blood	brain
6	brain, urinary,dental,eye,	US	Tube					urinary				brain
7	eye, dental, ear	Can	Drip		eye	ear	dental
8	eye, dental	Can	Drip		eye		dental
9	dental, urinary, brain, blood	Can	Drip				dental	urinary			blood	brain
Extract to column 2

@Muhammad_Usman
Re: Post 10
I'm not sure what you are using for your screen shots & formulas, but please look for options to restrict how many formulas you display, as I have done with my screen shots. There is generally no need to display multiple formulas that are basically all the same, it just fills up the board and makes your post and the thread harder to read/navigate.

 

[Jitka]

This is amazing, it took me 2 seconds with your formula. Thank you so much, clever mind. Jitka

 

[Jitka]

Thank you for your advice and a tip Peter. In awe of you all.
Kind Regards
Jitka

 

[Peter_SSs]

You are welcome, but looking back at my last post, I notice some of the words are missing that should be appearing in the right side of the sheet. That is because there is inconsistency in your data in terms of 'comma' or 'comma space' between the words in column C. If you do have inconsistency like that, you might need this variation of the formula.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N
1	Condition	Location	Feed		eye	ear	dental	urinary	forearm	headaches	blood	brain
2	dental, eye, forearm	UK	Nil		eye		dental		forearm
3	headaches, dental, eye	UK	Nil		eye		dental			headaches
4	blood,urinary, dental	UK	Nil				dental	urinary			blood
5	brain, blood, urinary, dental	US	Tube				dental	urinary			blood	brain
6	brain, urinary,dental,eye,	US	Tube		eye		dental	urinary				brain
7	eye, dental, ear	Can	Drip		eye	ear	dental
8	eye, dental	Can	Drip		eye		dental
9	dental, urinary, brain, blood	Can	Drip				dental	urinary			blood	brain
Extract to column 3