Help with excel.

sriharsha

sriharsha

Disciple
Hi all,

I need your help in creating a sound alike drug list from 10,000 brand names. This, thought has come into my mind after my patient was given with wrong drug.

After some search, I have come across this search string called "phonetic matching" - Soundex and
Beider-Morse Phonetic Matching. However, I have no idea how to run these codex in excel. in between, i m using MS office 2010.

Excel gurus, please help me with this.

Regards
Harsha
 
Last edited:
whatsinaname said:
Why Excel? Are you familiar with databases? SQL Server and MYSQL already have Soundex implemented. Load your data in and run a simple query.
Click to expand...

Thank you answering!

No, I am not familiar. They have an excel sheet and I want to do something out of it for patient safety.

Using SQL and MYSQL, are they easy?

I am complete novice in these issues. your further assistance is much appreciated.

Regards,
Harsha[DOUBLEPOST=1433959127][/DOUBLEPOST]
all4music said:
i found this while searching around.. dunno if it'll be of any help but you can give this code a try.
Click to expand...
HI,

Thank you for replying!

Umm, I cont find the Code and I dont know how to install the code into the excel.

Can you please help me with more details?

Warm Regards,
Harsha
 
sriharsha said:
Can you please help me with more details?
Click to expand...
i haven't gone through the code completely, but how do you want it to work.
-as you type, it should list the phonetically similar names ?

PS: oops! lloks like the webpage address never got pasted in my previous post!!
linky:http://j-walk.com/ss/excel/tips/tip77.htm

if you just want to list your phonetically sounding names together it 'might' be easy fix:
Ram R500
Raghuveer R216
Ravan R150
Ravi R100
Ravyan R150
Rashmi R250
Rekha R200
Rahsi R200

names and their numbering. their numbering is what determines their similarity. you can sort names based on these numberings. again this is what i can make out of it in the short span of time. give me [here] a parital list of drugs which contain phonetically similar ones and otherwise. let's see if this helps you?
 
Last edited:
@sriharsha - Hmm. I knew about soundex in mysql but I hadn't really looked at it more closely. Getting familiar enough with databases just to implement this might be a it counter productive in your work.

I used the link @all4music posted to create a sample Excel file. I've uploaded it here - try it out. Soundex doesn't seem to work that great, but maybe the kind of matches it found is what you were looking for. (Drug names that could be mistaken).

https://www.dropbox.com/s/b10rbgyescxiz4r/SOUNDEX.xlsm?dl=0
 
all4music and whatsinaname, Arigathoguzaimasu.

Yes, something very similar to that. but when the brand names come in... you know, limcee and lincef sound similar but their generics are vit. c and linezolid (antibiotic).

I am really grateful to see you helping me out.

please see this blog I have come across http://ntz-develop.blogspot.in/2011/03/phonetic-algorithms.html and here I am uploading the file which needs to be taken care.
https://www.dropbox.com/s/qnquqm8ncfl3217/Untitled Item Master.xls?dl=0

you have my deep regards.
Harsha[DOUBLEPOST=1433989895][/DOUBLEPOST]Hey, I just found this one too! http://www.comparisonics.com/search.html

Can we do something?
 
all4music said:
i haven't gone through the code completely, but how do you want it to work.
-as you type, it should list the phonetically similar names ?

PS: oops! lloks like the webpage address never got pasted in my previous post!!
linky:http://j-walk.com/ss/excel/tips/tip77.htm

if you just want to list your phonetically sounding names together it 'might' be easy fix:
Ram R500
Raghuveer R216
Ravan R150
Ravi R100
Ravyan R150
Rashmi R250
Rekha R200
Rahsi R200

names and their numbering. their numbering is what determines their similarity. you can sort names based on these numberings. again this is what i can make out of it in the short span of time. give me [here] a parital list of drugs which contain phonetically similar ones and otherwise. let's see if this helps you?
Click to expand...
all4music said:
i haven't gone through the code completely, but how do you want it to work.
-as you type, it should list the phonetically similar names ?

PS: oops! lloks like the webpage address never got pasted in my previous post!!
linky:http://j-walk.com/ss/excel/tips/tip77.htm

if you just want to list your phonetically sounding names together it 'might' be easy fix:
Ram R500
Raghuveer R216
Ravan R150
Ravi R100
Ravyan R150
Rashmi R250
Rekha R200
Rahsi R200

names and their numbering. their numbering is what determines their similarity. you can sort names based on these numberings. again this is what i can make out of it in the short span of time. give me [here] a parital list of drugs which contain phonetically similar ones and otherwise. let's see if this helps you?
Click to expand...

Hi,

Thank you for the efforts again. So, is there any algorithm that assigns the numbers automatically for the entities in the selected column? I have just shared the drop box link in a common reply.

Warm Regards
Harsha
 
sriharsha said:
So, is there any algorithm that assigns the numbers automatically for the entities in the selected column?
Click to expand...
hi Sriharsha,
there might be different phonetic algorithms around. some might be better than others. i have just used the function defined in the above link and have updated your excel file [Soundex algorithm].

Column 'C" has a formula which assigns a Soundex code for the word on Column 'B'. it doesn't work for words starting with numbers. I have sorted your list with Soundex numbering. hopefully it would be of some help.
https://www.dropbox.com/s/u6xj3zkh0nae4zu/Untitled Item Master_Soundex.xlsm?dl=0

please note: i have not developed the code, i have merely used the function coded by the developer in that linky!
 
all4music said:
hi Sriharsha,
there might be different phonetic algorithms around. some might be better than others. i have just used the function defined in the above link and have updated your excel file [Soundex algorithm].

Column 'C" has a formula which assigns a Soundex code for the word on Column 'B'. it doesn't work for words starting with numbers. I have sorted your list with Soundex numbering. hopefully it would be of some help.
https://www.dropbox.com/s/u6xj3zkh0nae4zu/Untitled Item Master_Soundex.xlsm?dl=0

please note: i have not developed the code, i have merely used the function coded by the developer in that linky!
Click to expand...

So in the column C, the numbers which are similar, sound similar. arent they?[DOUBLEPOST=1434020177][/DOUBLEPOST]Thank
sriharsha said:
So in the column C, the numbers which are similar, sound similar. arent they?Thank you so much :D :D :D
Click to expand...
[DOUBLEPOST=1434022039][/DOUBLEPOST]Thank you so much :D :D :D
 
Last edited:
asingh said:
Can you post an example here, what you want. Just type it out. No need for an attachment.
Click to expand...
Hi, I just saw your msg, it is like Limcee and Lincef. In busy day, pharmacist may dispense the later one for the limcee. so, I like that I want to separate the sound alikes. all4music has did a great job. if you 'd like to see to improve in anyway, you are most welcome.[DOUBLEPOST=1434035195][/DOUBLEPOST]
all4music said:
:) you are welcome!
Click to expand...

hi, can you please tell me this much, if I add a new entity in the first column will the soundex gets updated automatically?

Regards
 
sriharsha said:
if I add a new entity in the first column will the soundex gets updated automatically?
Click to expand...
if you are adding extra words on Column 'B' then you would have to just copy the existing Column 'C' formula paste it on the blank cells of Column 'C'.
later you can sort it on Column 'C' to maintain the order.
 
sriharsha said:
thank you, but can you please explain me in detail?
Click to expand...

It is a basic Excel formula. Imagine you have 1000 rows of drugs.

If you add a drug name in Cell B1001, there are two ways to do it.

1) In Cell C1001, type "=SOUNDEX(B1001)"
or
2) Copy Cell C1000 into C1001, Excel will automatically update the formula.
 
asingh said:
Is soundex a UDF in that excel sheet....?
Click to expand...

Yes, it is like user define function. but projects like soundex are coming in. Just that I dont know how to run it and all. Do you think we can i,prove it further? with less wrong hits (Soundex does that)

TE is always been helpful.

Thank you.[DOUBLEPOST=1434071443][/DOUBLEPOST]
whatsinaname said:
It is a basic Excel formula. Imagine you have 1000 rows of drugs.

If you add a drug name in Cell B1001, there are two ways to do it.

1) In Cell C1001, type "=SOUNDEX(B1001)"
or
2) Copy Cell C1000 into C1001, Excel will automatically update the formula.
Click to expand...

got it :) did it and came out great :) thank you so much!!!
 
Last edited:
Back
Top