Comparing srtings
Posted by Mayca Roig on January 02, 2002 4:58 PM
I have a spreadsheet with three columns: A, B, and C. Column A has info about medical complaints of people (i.e., A1="I have a stomacache", A2="My nose is bleeding", A3="I have leg cramps", etc.). Column "B", has a list of standard symtoms (B1="stomache", B2="headache", B3="cramps", etc). I want to do the following (with a function?)to get the value for Column C(I am using as an example the first row):
If any substring of A1 ("I have a stomache") matches any of the string in colums B ("stomache", headache", "cramps", etc), put in C1 the value of A1 ("I have a stomache"). In this case, "stomacache is found within ("I have a stomache"), therefore C1="I have a stomache".
Thank-you very much
Posted by Aladin Akyurek on January 02, 2002 5:04 PM
Mayca --
Wouldn't a symptom from B occur/appear more than once in A? If that's the case, what do you want to do with multiple occurrences?
Aladin
=========
Posted by Mayca Roig on January 02, 2002 5:11 PM
--If A1 has any of the substrings in B (B1, or B2, or B3.....), then C1=A1, then
--If A2 has any of the substrings in B (B1, or B2, or B3.....), then C1=A2
----If A3 has any of the substrings in B (B1, or B2, or B3.....), then C1=A3
and so on.
thank-you so very much, hope that I clarified what you were asking for.
Posted by Mayca Roig on January 02, 2002 5:13 PM
Re: Sorry, disregard previous message!!!
--If A1 has any of the substrings in B (B1, or B2, or B3.....), then C1=A1, then
--If A2 has any of the substrings in B (B1, or B2, or B3.....), then C2=A2
----If A3 has any of the substrings in B (B1, or B2, or B3.....), then C3=A3
and so on.
thank-you so very much, hope that I clarified what you were asking for.
Posted by Aladin Akyurek on January 02, 2002 5:23 PM
In C1 enter: =IF(COUNTIF(A:A,"*"&B1&"*")>0,INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"")
will give you the first string in A that has the symptom in B1.
Dubble click on the little black square in order to this formula down.
Multiple occurrences of a B-value in A can be retrieved to columns adjacent to C. That requires a more elaborate formula. If interested, let me know.
Aladin
========
Posted by Mayca Roig on January 02, 2002 5:35 PM
To find the value of C1, I have to compare A1 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C1=A1.
To find the value of C2, I have to compare A2 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C2=A2.
And so on.
Sorry for my mistake in a previous message.
Mayca
Posted by Mayca Roig on January 02, 2002 5:35 PM
To find the value of C1, I have to compare A1 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C1=A1.
To find the value of C2, I have to compare A2 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C2=A2.
And so on.
Sorry for my mistake in a previous message.
Mayca
Posted by Mayca roig on January 02, 2002 5:37 PM
Re: Correction
:In my previous message, where I said "A!" I meant "A1".
Mayca Aladin. I could not come with this sort of functions on my own. Now, I do not get still the right result, I am afraid I need the more complex formula you were talking about. Literally, I need the following: To find the value of C1, I have to compare A1 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C1=A1. To find the value of C2, I have to compare A2 to all and each value on B. If any of the strings on B (B1, or B2, or B3,...)is within the string in A!, then C2=A2. And so on. Sorry for my mistake in a previous message. Mayca
Posted by Aladin Akyurek on January 02, 2002 6:28 PM
Re: Correction
Hmmm...
In C1 enter: =IF(SUMPRODUCT(MAX(ISNUMBER(SEARCH($B$1:$B$100,A1)))),A1,"")
This formula looks if any symptom in B1:B100 is in A1; if so it returns A1 in C1, otherwise nothing (a blank).
Hope this is what you're looking for. If not, send me a copy of your worksheet/workbook with some expected results.
Aladin
========= :In my previous message, where I said "A!" I meant "A1". Mayca Aladin. I could not come with this sort of functions on my own. Now, I do not get still the right result, I am afraid I need the more complex formula you were talking about. Literally, I need the following
Posted by Aladin Akyurek on January 03, 2002 2:48 PM
Sequel (Re: Correction)
It appears that, yesterday, I posted the wrong formula.
Mayca provided (off line) the following sample data with the expected results in the 3rd column:
{"suffers from frequent headaches","nauseas","suffers from frequent headaches";
"memory problems","dizziness",0;
"gets dizzy in the mornings","cramps","gets dizzy in the mornings";
"has leg cramps","infection","has leg cramps";
"has an ear infection","headache","has an ear infection";
"back pain",0,0;
"cramps in his legs",0,"cramps in his legs";
"has nauseas in the morning",0,"has nauseas in the morning"}
Zeroes stand for empty cells.
The sample data with expected results occupy A1:C8, where A houses the "complaints", B the "symptoms", and C the expected results.
The formula (that I should have posted but failed to) is
[a] =IF(SUMPRODUCT((ISNUMBER(SEARCH($B$1:$B$5,A1))+0)),A1,"")
which is to be entered in D1 and copied down. It generates the following results:
{"suffers from frequent headaches";
"";
"";
"has leg cramps";
"has an ear infection";
"";
"cramps in his legs";
"has nauseas in the morning"}
As a comparison with expected results would show, it cannot match the pair
"gets dizzy in the mornings"
and
"dizziness"
that is, it cannot see that "dizzy" and "dizziness" are the same thing.
The following is a practical solution:
[b] =IF(SUMPRODUCT((ISNUMBER(SEARCH(LEFT($B$1:$B$5,4),A1))+0)),A1,"")
which compares the first 4 letters of a symptom with the complaints. It produces (for the sample data) the complete results.
Aladin
============ Hmmm... In C1 enter: =IF(SUMPRODUCT(MAX(ISNUMBER(SEARCH($B$1:$B$100,A1)))),A1,"") This formula looks if any symptom in B1:B100 is in A1; if so it returns A1 in C1, otherwise nothing (a blank). Hope this is what you're looking for. If not, send me a copy of your worksheet/workbook with some expected results.
