Help with excel.

sriharsha · Jun 10, 2015

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

asingh · Jun 10, 2015

Can you post an example here, what you want. Just type it out. No need for an attachment.

whatsinaname · Jun 10, 2015

Why Excel? Are you familiar with databases? SQL Server and MYSQL already have Soundex implemented. Load your data in and run a simple query.

all4music · Jun 10, 2015

i found this while searching around.. dunno if it'll be of any help but you can give this code a try.

http://j-walk.com/ss/excel/tips/tip77.htm

sriharsha · Jun 10, 2015

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.

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.

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

all4music · Jun 10, 2015

sriharsha said:
Can you please help me with more details?

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?

whatsinaname · Jun 11, 2015

@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

sriharsha · Jun 11, 2015

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?

sriharsha · Jun 11, 2015

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?

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

all4music · Jun 11, 2015

sriharsha said:
So, is there any algorithm that assigns the numbers automatically for the entities in the selected column?

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!

sriharsha · Jun 11, 2015

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!

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

[DOUBLEPOST=1434022039][/DOUBLEPOST]Thank you so much

all4music · Jun 11, 2015

sriharsha said:
Thank you so much

you are welcome!

sriharsha · Jun 11, 2015

asingh said:
Can you post an example here, what you want. Just type it out. No need for an attachment.

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!

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

all4music · Jun 11, 2015

sriharsha said:
if I add a new entity in the first column will the soundex gets updated automatically?

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 · Jun 11, 2015

all4music said:
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.

thank you, but can you please explain me in detail?

whatsinaname · Jun 11, 2015

sriharsha said:
thank you, but can you please explain me in detail?

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 · Jun 11, 2015

Is soundex a UDF in that excel sheet....?

sriharsha · Jun 12, 2015

asingh said:
Is soundex a UDF in that excel sheet....?

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.

got it

did it and came out great

thank you so much!!!