How to create a database of medicines (name, form, dose) comparing multiple cells in a row across different excel worksheets (each worksheet = country

[MeenuBala]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; min-height: 15.0px}</style>I am trying to develop a database that can help to analyze national essential medicine lists, to use as a policy tool to help improve access to medicines. Your help would be greatly appreciated.


I'd like to create a database comparing multiple medicine lists, where for each row, a medicine name (paracetamol), form (tablet), and dose (500mg) are given. If in two worksheets I have two medicine lists (one from WHO and one from a country like India), how can I create a comparison column for medicine names (any paracetamol in WHO list or India list), medicine name+form (any paracetamol tablet in WHO list or India list), medicine name+form+dose (any paracetamol tablet 500mg in WHO list or India list)? Eventually I would like to add many countries, and be able to answer questions such as which have paracetamol (any form or dose), paracetamol tablets (any dose), paracetamol tablets 500 mg?


Right now I have the lists in Excel, but they must be standardized (data cleaning). I would like to figure out if the comparisons above can be made across different cells (with each row being a product = medicine name+form+dose). I have tried to understand if a relational database and formulas and unique IDs would work -- but I'm not sure if all the three levels need unique IDs -- the medicine name, the medicine name + form, the medicine name + form + dose.


Otherwise I will manually compare the lists, but this could take too much time and create many errors.


I used the following formula to match between two lists, but this only worked comparing one cell, medicine name


=IF(ISERROR(VLOOKUP([@INN],BANNEML2017[[#All],[INNBAN]], 1, FALSE)),0,1 )

WHERE,
INN = medicine name in WHO List
BANNEML2017 = workbook for Bangladesh
INNBAN = medicine name in Bangladesh list




Here is a sample of the source data for WHO and India described above:


http://www.who.int/medicines/publications/essentialmedicines/20th_EML2017.pdf?ua=1

http://cdsco.nic.in/WriteReadData/NLEM-2015/NLEM, 2015.pdf

 

[BrianJN1]

I don't know if it would assist your cause but might you also be able to post a sample of your workbook?

I seem to understand that it comprises of two sheets of data, WHO and Bangladesh, and maybe a third where your hope to draw the comparisons?

Looking at the PDF data is somewhat confusing but to see what you are attempting to build (on a small scale) might make things clearer.

 

[MeenuBala]

SAMPLE DATA HERE:
https://drive.google.com/file/d/0B7u_48DE4lYWRTNSRVFwN0F5T0k/view?usp=sharing

Sorry for the long pause. I tried to create a simple worksheet with sample short lists attached. All of the data is publicly available.

My BIG question is if I have individual lists, is there a way to automate the creation of the MATCH and NON-MATCH sheets? As a possible solution, I thought I could use the MATCH and NON-MATCH sheets as a database to create pivot tables as needed.

In the end, I am very keen to create the database the easiest and most accurate way. There are 11 different countries. I have put effort to try to make all the data into EXcel sheets.

As you can see from the previous post, the lists are PDFs, at least 4 aren't even searchable but were photocopies and had be converted using OCR to make a Excel sheet. Some had to be translated from other languages.

Before cleaning up the individual lists from countries, I wanted to understand if standardizing the lists (such as the categories and medicine name and dosages) would be the best way forward. Otherwise, I will manually have to code the 1200 products from the reference WHO list X 11 countries, and keep a separate file for the products that are not in the WHO list but across the other countries.​

<fieldset class="postcontent" style="margin: 5px 0px; padding: 5px 10px; border-width: 0px; border-style: initial; border-color: initial; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-radius: 5px;"></fieldset>

 

[BrianJN1]

Thank you for your spreadsheet and explanations.

Unfortunately this is well beyond my scope of expertise but what you have provided should well guide one who is more knowledgeable.

I admire your cause and I wish you the best in a speedy result.

 

[MeenuBala]

Thank you BrianJN1 for the interest! It means a lot!