Results 1 to 14 of 14
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Marking-removing-filtering unwanted data from a long list

    I've been asked to do some research where I look at prescription drugs of a certain type, and what was prescribed at the same time. It's a big file and I'd like to trim it down. I've attached a small file that tries to explain. Is there a way to "anchor" the first drug, Drug A, look for matching fill dates until you get to the next Drug A prescription, and discarding the prescriptions for which the fill dates don't match?

    Thank you kindly,

    Jim Whitt, R.Ph.
    Temple, TX USA
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    ..you could add a formula which returns a True/False value, then just apply a filter on the True results. For example, in column [G] of your example file, add the formula in cell [G2]
    =COUNTIFS(A:A,A2,E:E,E2)>1
    ..and copy down as required.

    see attached file

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    ..the formula checks for multiple drug records (i.e. more than I drug) issued to the same patient on the same day.

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    ..and just a reminder, a quick way to 'copy down' formulas (especially if you have hundreds or thousands of rows) is to select the cell containing the formula. Then move your mouse pointer to the bottom-right-corner of the formula cell (the cellpointer 'changes' to a 'plus') and then double-click the mouse in this bottom-right-corner.

    zeddy

  5. #5
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Jim

    ..and just a reminder, a quick way to 'copy down' formulas (especially if you have hundreds or thousands of rows) is to select the cell containing the formula. Then move your mouse pointer to the bottom-right-corner of the formula cell (the cellpointer 'changes' to a 'plus') and then double-click the mouse in this bottom-right-corner.

    zeddy
    Thanks a lot, Zeddy. I'm not clear on how to tell Excel to change the anchor cell, but the formula seems to work anyway. A new spreadsheet is attached.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    So, do you only want to see records where any patient has received multiple drugs on a day, and on that day the drugs included a specified drug?

    i.e. so we need to ignore all records where only a single drug was given on that day (i.e. even if that single drug was the 'specified drug'), and ignore all records where multiple drugs were given on that day but these drugs didn't include the 'specified drug' we are interested in?

    If so, we can tune up the results further.

    zeddy

  7. #7
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That is correct, Zeddy. I'm trying to wrap my brain around what happens when the patient changes and we have a new anchor prescription. Appreciate your help.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    Are your data records sorted?
    Approx. how many records do we have e.g. is it ~100, or ~1,000 or ~10,000 or 100,000 or 1,000,000 (it makes a difference to the solution method)

    zeddy

  9. #9
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Jim

    Are your data records sorted?
    Approx. how many records do we have e.g. is it ~100, or ~1,000 or ~10,000 or 100,000 or 1,000,000 (it makes a difference to the solution method)

    zeddy
    There are just over 250,000 rows. The data right now is sorted by target drug (those are in a different color font), then by SSN of the patient, then the fill date of the prescription. I've tinkered with other ways.

    I'm sorry I didn't respond sooner. I have been fighting the flu. Appreciate your help!!!

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jim

    Don't let the flu win.

    Here's the latest instalment..

    In the attached file I have added some simple macros to show a filtered record list which matches certain conditions.
    Click the [formula columns] button to show/hide columns G, H, I

    The formula in Column [G] is used to identify records where more than one drug was issued to the same Patient on the same day.
    The formula in column [H] is used to identify if the drug is included in the list 'of a certain type'.
    The formula in column [I] is used to identify which records to display.

    On the sheet named [drugs], there is a list of drugs 'of a certain type'. We are interested in showing records where multiple drugs have been issued on the same date to the same patient, and those drugs issued on that date include at least one of the drugs in the specified list.

    You can add/remove drugs from the named range [drugList] as required, on sheet [drugs], and then click the buttons to re-filter the matching records.

    NOTES:
    A record will be hidden if only a single drug was issued to a patient on that particular date (even if that particular drug itself was on the specified list - since we are only interested in multiple drugs given to the same patient on the same date).

    It is assumed that it is possible that the patient could be given more than one of the specified drugs on the same date. (The formula in column [I] takes this into account).

    Use the attached demo file to see if this does what you want.

    zeddy
    Attached Files Attached Files

  11. #11
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you, Zeddy, for both the advice and the file. I've been working with Excel for twenty years, and I've never imagined anything like that group of formulae. I made a copy of the file to practice on until I'm ready to go live. I'm getting my stamina back day by day; thanks for the encouragement.

  12. #12
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by SoonerJim View Post
    Thank you, Zeddy, for both the advice and the file. I've been working with Excel for twenty years, and I've never imagined anything like that group of formulae. I made a copy of the file to practice on until I'm ready to go live. I'm getting my stamina back day by day; thanks for the encouragement.
    My boss has asked me to take a different approach. He wants to look for prescriptions on my list that have the reason the patient is taking the medicine; call it "onion allergies". If that is in the instructions field of the prescription, it won't matter what other drugs were prescribed that day. I'd like to make it elegant and show the other drugs prescribed that day, but what my boss wants cuts it down to the bone.

  13. #13
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    Jim,

    Most times when I'm faced with creating sheets that are defined by multiple selection criteria, I turn to pivot tables. With pivot tables I can generally organize the fields to auto-select the criteria. The enclosed spreadsheet has two versions of a pivot table that would meet your bosses most recent request, and also allow you to see other versions of the data with little more than choosing criteria for the pivot-tables built-in filters. The pivot table will also let you respond quickly to your bosses change of mind in what data should be displayed and how it should be arranged. I've had many such bosses. I saved the sheet in a macro-free form.

    Norm
    Attached Files Attached Files

  14. #14
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Norm, many thanks for your post. I have used pivot tables for many years, usually to collect and organize data; i.e., number crunching. I will give that a look on my workbook.

Posting Permissions

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