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

 

[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

[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Reporting Period[/TD]
[TD]Headaches[/TD]
[TD]Dental[/TD]
[TD]Eye[/TD]
[TD]Location[/TD]
[TD]Feed[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]June 18[/TD]
[TD]headaches[/TD]
[TD]dental[/TD]
[TD]eye[/TD]
[TD]UK[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]June 17[/TD]
[TD][/TD]
[TD]dental[/TD]
[TD][/TD]
[TD]UK[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]ID2 [/TD]
[TD]June 18[/TD]
[TD][/TD]
[TD]dental[/TD]
[TD][/TD]
[TD]US [/TD]
[TD]Tube[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]May 17[/TD]
[TD][/TD]
[TD]dental[/TD]
[TD]eye[/TD]
[TD]US[/TD]
[TD]Tube[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]April 18[/TD]
[TD][/TD]
[TD]dental[/TD]
[TD]eye[/TD]
[TD]Can[/TD]
[TD]Drip[/TD]
[/TR]
[TR]
[TD]ID3 [/TD]
[TD]April 17[/TD]
[TD][/TD]
[TD]dental[/TD]
[TD]eye[/TD]
[TD]Can[/TD]
[TD]Drip[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Name	Reporting period	Condition	Location	Feed	headaches	blood	brain	eye	dental	urinary
ID1	headaches, dental, eye	UK	Nil	headaches			eye	dental
ID1	blood,urinary, dental	UK	Nil		blood			dental	urinary
ID2	brain, blood, urinary, dental	US	Tube		blood	brain		dental	urinary
ID2	brain, urinary,dental,eye,	US	Tube			brain	eye	dental	urinary
ID3	eye, dental	Can	Drip				eye	dental
ID3	eye, dental	Can	Drip				eye	dental
ID3	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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18-Jun[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]17-Jun[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18-Jun[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]17-May[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18-Apr[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]17-Apr[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]15-Dec[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C2)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C2)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C2)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C2)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C2)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C2)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C3)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C3)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C3)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C3)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C3)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C3)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C4)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C4)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C4)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C4)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C4)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L4[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C4)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C5)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C5)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C5)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C5)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C5)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L5[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C5)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C6)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C6)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C6)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C6)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C6)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C6)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C7)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C7)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C7)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C7)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C7)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L7[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C7)),L$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(G$1,$C8)),G$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(H$1,$C8)),H$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(I$1,$C8)),I$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(J$1,$C8)),J$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(K$1,$C8)),K$1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L8[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(L$1,$C8)),L$1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]