+ Reply to Thread
Results 1 to 5 of 5

Comparing two lists to find matches

  1. #1
    Registered User
    Join Date
    06-11-2019
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    3

    Comparing two lists to find matches

    I have two spreadsheets: one with information about patients' prescriptions and the other about patients' diagnoses. I want to find the matches from one list to the next - in other words, how can I find the patients in the diagnosis spreadsheet who also have a prescription, listed in the other spreadsheet? There are matching patients between these lists and I want to pull out the ones that have both the diagnosis and the prescription. I have tried conditional formatting with highlighting duplicates (too much data and repeat info because there are multiple fill dates for one patient, for example) and conditional formatting with the highlight function using a match formula (conditional formatting > new rule > use a formula to determine which cells to format > match($A2, $1$2:$I$350,0). The latter formula works mostly well, but will not highlight all of the matches (no idea why...). Any help would be very much appreciated...!!
    Last edited by mheim; 06-11-2019 at 02:41 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Comparing two lists to find matches

    Power Query seems to work well for comparing lists.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    06-11-2019
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    3

    Re: Comparing two lists to find matches

    Thanks. Attached is an example (not PHI!) of what I am working with. But my actual spreadsheet is much bigger up to 4800 rows.
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Comparing two lists to find matches

    You didn't include the desired results so I took a guess at what you are looking for.

    I moved the two ranges into their own worksheets.

    How about this in E2 of the PRESCRIPTIONS sheet?

    =IF(COUNTIF(DIAGNOSIS!A:A,A2),"Yes","No")

    This will give you a column of Yes' and No's.

    See attachment.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-11-2019
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    3

    Re: Comparing two lists to find matches

    Thank you very much, 63falcondude!

+ 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. [SOLVED] Compare two lists of words to find closest matches
    By elise.benjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2015, 10:02 AM
  2. Comparing Data between sheets to find matches and no matches
    By tysont in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-26-2015, 03:07 PM
  3. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  4. Comparing 3 or more columns to find matches
    By Spinnel in forum Excel General
    Replies: 7
    Last Post: 01-19-2009, 08:50 AM
  5. Comparing Columns to Find Matches
    By cultavix in forum Excel General
    Replies: 1
    Last Post: 11-07-2008, 11:06 AM
  6. comparing two lists for matches
    By Conor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2008, 11:54 AM
  7. Find matches between lists
    By mustangcobra44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-14-2007, 01:03 PM

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