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

[Jitka]

Name	Reporting period	Condition	Location	Feed
ID1	June 18	headaches, dental, eye	UK	Nil
ID1	June 17	blood,urinary, dental	UK	Nil
ID2	June 18	brain, blood, urinary, dental	US	Tube
ID2	May 17	brain, urinary,dental,eye,	US	Tube
ID3	April 18	eye, dental	Can	Drip
ID3	April 17	eye, dental	Can	Drip
ID3	Dec 15	dental, urinary, brain, blood	Can	Drip

<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

 

[Muhammad_Usman]

Dear Jitka! , Please share you required result in tabular form!

 

[Jitka]

Hi, thanks very much for the quick response.

In order to work into pivot, I need something like this (I need column for every single condition listed to enable me to pull it into a graph). for this purposed I created only 3 different conditions. In the cells below the conditions headers i.e. headaches - I either want the name of condition or just a mark each time it's named, I basically need to be able to count the conditions.
Thank you, I hope the below is sufficient?
Jitka

Name	Reporting Period	Headaches	Dental	Eye	Location	Feed
ID1	June 18	headaches	dental	eye	UK	Nil
ID1	June 17		dental		UK	Nil
ID2	June 18		dental		US	Tube
ID2	May 17		dental	eye	US	Tube
ID3	April 18		dental	eye	Can	Drip
ID3	April 17		dental	eye	Can	Drip

<tbody>
</tbody>

 

[Jitka]

hello

can anyone help please? I am stuck and doing this manually is so time intensive. Thank you

 

[Muhammad_Usman]

Hi Jitka, I was working on your problem, but busy in office work so that's y could not respond.
Can you mention total conditions. headaches,dental,eye ... how many total conditions there you are working on?

 

[Jitka]

Thank you Muhammad

this may vary depending on how many are selected on the database and how many clients I have each quarter. I have other spreadsheet where I have the same issue, just different text (as on my cloud database, there is an option for multipicklist and when exported to excel, the multipicklist goes into 1 cell for each each client). So knowing how to process this would be amazing.
Thank you
Have a great day
Jitka

 

[nikio8]

Hi,
Here is possible solution, a function saved in a macro enabled file. GKW will get you keywords.
add new columns with condition heading.
Now type =gkw($C2,F$1)
C is condition row, F is heading. Drag right and down.

Now to check you got all conditions, we can use second function. if there are 4 words, there should be 4 non empty entries in conditions columns.

Function GKW(Condition As String, FieldWord As String) As String
    If InStr(LCase(Condition), LCase(FieldWord)) Then
        GKW = FieldWord
    Else
        GKW = ""
    End If
    
End Function


Function CountWords(Condition As String) As Integer
    CountWords = UBound(Split(Condition)) + 1
End Function

 

[nikio8]

https://imgur.com/a/3FMjwXy

Here we go,
=CountWords(C2)-(COUNTA(F2:K2)-COUNTIF(F2:K2,""))
note that you have to separate words with space.

if you are happy that it all works as planed,
=CountWords(C2)-SUM(F2:K2)

Function GKW(Condition As String, FieldWord As String) As Integer
    If InStr(LCase(Condition), LCase(FieldWord)) Then
        GKW = 1
    Else
        GKW = 0
    End If
    
End Function

 

[nikio8]

https://imgur.com/a/3FMjwXy

Here we go,
=CountWords(C2)-(COUNTA(F2:K2)-COUNTIF(F2:K2,""))
note that you have to separate words with space.

if you are happy that it all works as planed,
=CountWords(C2)-SUM(F2:K2)

Function GKW(Condition As String, FieldWord As String) As Integer
    If InStr(LCase(Condition), LCase(FieldWord)) Then
        GKW = 1
    Else
        GKW = 0
    End If
    
End Function

if you are not comfortable with vba, this formula works too
=IF(IFERROR(SEARCH(F$1,$C2)>0,FALSE),1,0)

 

[Muhammad_Usman]

Thank you Muhammad

this may vary depending on how many are selected on the database and how many clients I have each quarter. I have other spreadsheet where I have the same issue, just different text (as on my cloud database, there is an option for multipicklist and when exported to excel, the multipicklist goes into 1 cell for each each client). So knowing how to process this would be amazing.
Thank you
Have a great day
Jitka

Dear Jitka , May b this will help full for you. Its easy and one time working... You have to just enter your condition From G1 to onward. Paste following formula in G2. Drag it downward and rightward.Hopefully you want this one.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Reporting period	Condition	Location	Feed		headaches	blood	brain	eye	dental	urinary
2	ID1	18-Jun	headaches, dental, eye	UK	Nil		headaches			eye	dental
3	ID1	17-Jun	blood,urinary, dental	UK	Nil			blood			dental	urinary
4	ID2	18-Jun	brain, blood, urinary, dental	US	Tube			blood	brain		dental	urinary
5	ID2	17-May	brain, urinary,dental,eye,	US	Tube				brain	eye	dental	urinary
6	ID3	18-Apr	eye, dental	Can	Drip					eye	dental
7	ID3	17-Apr	eye, dental	Can	Drip					eye	dental
8	ID3	15-Dec	dental, urinary, brain, blood	Can	Drip			blood	brain		dental	urinary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =IF(ISNUMBER(SEARCH(G$1,$C2)),G$1,"") H2 =IF(ISNUMBER(SEARCH(H$1,$C2)),H$1,"") I2 =IF(ISNUMBER(SEARCH(I$1,$C2)),I$1,"") J2 =IF(ISNUMBER(SEARCH(J$1,$C2)),J$1,"") K2 =IF(ISNUMBER(SEARCH(K$1,$C2)),K$1,"") L2 =IF(ISNUMBER(SEARCH(L$1,$C2)),L$1,"") G3 =IF(ISNUMBER(SEARCH(G$1,$C3)),G$1,"") H3 =IF(ISNUMBER(SEARCH(H$1,$C3)),H$1,"") I3 =IF(ISNUMBER(SEARCH(I$1,$C3)),I$1,"") J3 =IF(ISNUMBER(SEARCH(J$1,$C3)),J$1,"") K3 =IF(ISNUMBER(SEARCH(K$1,$C3)),K$1,"") L3 =IF(ISNUMBER(SEARCH(L$1,$C3)),L$1,"") G4 =IF(ISNUMBER(SEARCH(G$1,$C4)),G$1,"") H4 =IF(ISNUMBER(SEARCH(H$1,$C4)),H$1,"") I4 =IF(ISNUMBER(SEARCH(I$1,$C4)),I$1,"") J4 =IF(ISNUMBER(SEARCH(J$1,$C4)),J$1,"") K4 =IF(ISNUMBER(SEARCH(K$1,$C4)),K$1,"") L4 =IF(ISNUMBER(SEARCH(L$1,$C4)),L$1,"") G5 =IF(ISNUMBER(SEARCH(G$1,$C5)),G$1,"") H5 =IF(ISNUMBER(SEARCH(H$1,$C5)),H$1,"") I5 =IF(ISNUMBER(SEARCH(I$1,$C5)),I$1,"") J5 =IF(ISNUMBER(SEARCH(J$1,$C5)),J$1,"") K5 =IF(ISNUMBER(SEARCH(K$1,$C5)),K$1,"") L5 =IF(ISNUMBER(SEARCH(L$1,$C5)),L$1,"") G6 =IF(ISNUMBER(SEARCH(G$1,$C6)),G$1,"") H6 =IF(ISNUMBER(SEARCH(H$1,$C6)),H$1,"") I6 =IF(ISNUMBER(SEARCH(I$1,$C6)),I$1,"") J6 =IF(ISNUMBER(SEARCH(J$1,$C6)),J$1,"") K6 =IF(ISNUMBER(SEARCH(K$1,$C6)),K$1,"") L6 =IF(ISNUMBER(SEARCH(L$1,$C6)),L$1,"") G7 =IF(ISNUMBER(SEARCH(G$1,$C7)),G$1,"") H7 =IF(ISNUMBER(SEARCH(H$1,$C7)),H$1,"") I7 =IF(ISNUMBER(SEARCH(I$1,$C7)),I$1,"") J7 =IF(ISNUMBER(SEARCH(J$1,$C7)),J$1,"") K7 =IF(ISNUMBER(SEARCH(K$1,$C7)),K$1,"") L7 =IF(ISNUMBER(SEARCH(L$1,$C7)),L$1,"") G8 =IF(ISNUMBER(SEARCH(G$1,$C8)),G$1,"") H8 =IF(ISNUMBER(SEARCH(H$1,$C8)),H$1,"") I8 =IF(ISNUMBER(SEARCH(I$1,$C8)),I$1,"") J8 =IF(ISNUMBER(SEARCH(J$1,$C8)),J$1,"") K8 =IF(ISNUMBER(SEARCH(K$1,$C8)),K$1,"") L8 =IF(ISNUMBER(SEARCH(L$1,$C8)),L$1,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>