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

J

Jitka

New Member
Joined
Jul 31, 2018
Messages
24
NameReporting periodConditionLocationFeed
ID1June 18headaches, dental, eyeUKNil
ID1June 17blood,urinary, dentalUKNil
ID2June 18brain, blood, urinary, dentalUSTube
ID2May 17brain, urinary,dental,eye,USTube
ID3April 18eye, dentalCanDrip
ID3April 17eye, dentalCanDrip
ID3Dec 15dental, urinary, brain, bloodCanDrip

<tbody>
</tbody>

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
 
Sort by date Sort by votes
wow, Muhammad, you are using my idea, just slightly different.
not even using a test is all keywords were used up.:oops:
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
nikio8 said:
wow, Muhammad, you are using my idea, just slightly different.
not even using a test is all keywords were used up.:oops:
Click to expand...


Yes Bro in excel you can do same thing with different methods....And i think its easy to use just formula instead using codes... :cool::cool::cool:
 
Upvote 0
Muhammad_Usman said:
Yes Bro in excel you can do same thing with different methods....And i think its easy to use just formula instead using codes... :cool::cool::cool:
Click to expand...

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 .
 
Upvote 0
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
CDEFGHIJK
1ConditionLocationFeedeyeeardentalurinaryforearm
2dental, eye, forearmUKNileyeeardentalforearm
Extract to column 1



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

Excel Workbook
CDEFGHIJKLMN
1ConditionLocationFeedeyeeardentalurinaryforearmheadachesbloodbrain
2dental, eye, forearmUKNileyedentalforearm
3headaches, dental, eyeUKNileyedentalheadaches
4blood,urinary, dentalUKNildentalblood
5brain, blood, urinary, dentalUSTubedentalurinarybloodbrain
6brain, urinary,dental,eye,USTubeurinarybrain
7eye, dental, earCanDripeyeeardental
8eye, dentalCanDripeyedental
9dental, urinary, brain, bloodCanDripdentalurinarybloodbrain
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.
 
Upvote 0
This is amazing, it took me 2 seconds with your formula. Thank you so much, clever mind. Jitka :):):)
 
Upvote 0
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
CDEFGHIJKLMN
1ConditionLocationFeedeyeeardentalurinaryforearmheadachesbloodbrain
2dental, eye, forearmUKNileyedentalforearm
3headaches, dental, eyeUKNileyedentalheadaches
4blood,urinary, dentalUKNildentalurinaryblood
5brain, blood, urinary, dentalUSTubedentalurinarybloodbrain
6brain, urinary,dental,eye,USTubeeyedentalurinarybrain
7eye, dental, earCanDripeyeeardental
8eye, dentalCanDripeyedental
9dental, urinary, brain, bloodCanDripdentalurinarybloodbrain
Extract to column 3
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Splitting text into columns where text is located across multiple adjacent cells?
Replies
7
Views
841
Peter_SSs
Peter_SSs
L
  • Solved
Split comma separated text into multiple columns
Replies
3
Views
4K
Fluff
Fluff
R
  • Question
how to: If formula with 3 conditions on multiple columns to create a pivot table
Replies
0
Views
819
raul8
R
L
  • Solved
Splitting multiple choice entries from Microsoft Forms in Excel without text to columns
Replies
1
Views
6K
Lebene
L
O
  • Question
(Power?) pivot: filter or split comma separated data in one cell to multiple columns in (power?) pivot table (by measure?)
Replies
0
Views
921
ooptennoort
O

Forum statistics

Threads
1,215,734
Messages
6,126,542
Members
449,316
Latest member
sravya

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top