+ Reply to Thread
Results 1 to 5 of 5

How to create a database comparing multiple cells in a row in Excel for Mac 2011?

  1. #1
    Registered User
    Join Date
    10-26-2017
    Location
    Delhi, India
    MS-Off Ver
    MAC 2011
    Posts
    3

    How to create a database comparing multiple cells in a row in Excel for Mac 2011?

    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 am using Excel 2011 for MAC.


    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/publica...L2017.pdf?ua=1

    http://cdsco.nic.in/WriteReadData/NL...EM,%202015.pdf

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: How to create a database comparing multiple cells in a row in Excel for Mac 2011?

    Please attach your worksheet
    To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
    Don't forget to Mention your desired result in the sheet..
    There should be sample data only, a lot of data creates confusion.

    Thanks & Regards

    Shivya

    http://excelvbatipsforbeginners.blogspot.in/

  3. #3
    Registered User
    Join Date
    10-26-2017
    Location
    Delhi, India
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: How to create a database comparing multiple cells in a row in Excel for Mac 2011?

    Thank you, Shivya.

    Sorry I am not sure how to attach the worksheet, so i uploaded as a google drive file with this link:

    https://drive.google.com/file/d/0B7u...ew?usp=sharing

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: How to create a database comparing multiple cells in a row in Excel for Mac 2011?

    You can create a helper column for adding more criteria.

    You just need to upload a sample file, your uploaded file is too big with lots of rough data which makes big confusion.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Registered User
    Join Date
    10-26-2017
    Location
    Delhi, India
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: How to create a database comparing multiple cells in a row in Excel for Mac 2011?

    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?
    I would like to 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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA create txt file from cell contents on Excel 2011 (Mac) VBA
    By lw5182 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2013, 07:07 AM
  2. Mac Excel 2011 - How To Create Shortcuts When Entering Variables
    By davidosus in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 09-09-2013, 03:24 AM
  3. Can't create Mac query files for Excel for Mac 2011
    By veronicahope in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-05-2013, 02:55 AM
  4. Create macro to selet Printer for excel 2011 version
    By Zorro9758 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2013, 02:13 AM
  5. VBA EXCEL 2011 for Macs- Database Development Methods???
    By MarkWil in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-15-2012, 09:11 AM
  6. How do I create a user-defined function in Excel 2011?
    By dpbsmith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2012, 06:26 PM
  7. Replies: 8
    Last Post: 06-07-2012, 09:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1