+ Reply to Thread
Results 1 to 8 of 8

Compare 2 tables

  1. #1
    Registered User
    Join Date
    08-06-2018
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Compare 2 tables

    Hi,

    Apologies for the thread title - unsure how to word this.

    I have two tables with patient data - one called treatment data & one called weighing data.

    Patient Number Patient Treatment Treatment Date Weight
    1 T1 01/01/2019 97
    1 T1 02/01/2019 77
    4 T1 05/01/2019 90
    5 T1 05/01/2019 87
    3 T1 06/01/2019 88
    2 T1 04/01/2019 69


    Patient Number Weight Weigh Date Height
    3 98 01/01/2019 97
    3 62 02/01/2019 77
    1 70 03/01/2019 90
    4 89 05/01/2019 87
    5 77 06/01/2019 88
    2 65 04/01/2019 69

    I'd like a query/formula that for this example returns patient 4 and patient 2 as they had treatment and a weight recorded on the same date.

    Thanks!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Compare 2 tables

    I was about to say, "Boy, those are some odd shaped people you got there." Then I noticed the date format and the location in your profile. Those are kgs not lbs. and cm not inches.

    Please provide this information in an attached spreadsheet. I would have to copy / paste then text to columns then convert the dates to get this into a workbook. A real workbook would carry the dates as numbers internally which means the differences in our regional settings would not matter.

    Attach a sample workbook (not a picture or pasted copy). 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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 2 tables

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    08-06-2018
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Re: Compare 2 tables

    Hi, thanks both for replies, I think I've uploaded the file now?

    Edit: Yes. Struggling with Edge today.

    Edit 2: Within the file I've highlighted records that I'm interested in. Patient 1 had an appointment on 22-02-18 - further down we have an SADATE for 22-02-18 for patient 1, meaning they were weighed on a date that they also had an appointment on. The appointment could be any date, it's just that the weigh date has to match an appointment date for the patient - they're the records I'd like to establish with my larger dataset. If possible!
    Attached Files Attached Files
    Last edited by nffc; 02-14-2019 at 10:37 AM.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Compare 2 tables

    First I converted the data into an Excel Table. Excel Tables have a lot of advantages, three of them being.
    - They know how big they are so pivot tables, charts and formulas do not have to be changed when rows are added or deleted.
    - They copy down formulas, formats and validations automatically as rows are added.
    - You can use column headers in formulas and VB code which makes them easier to understand and program.

    Here is more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

    This spreadsheet makes use of an array formula: =ISNUMBER(MATCH([@SADATE],IF([PATIENT]=[@PATIENT],[APPT_DATE],FALSE),0))

    An array formula works on an array of cells rather than single cells and is "activated" by pressing CTRL_SHIFT_ENTER instead of just ENTER.

    The IF part of this formula states that I am only interested in looking at appointment dates for rows where the patient name is the same as the current row.

    The MATCH portion returns a the number of the first row on which the SADATE matchs APPT_DATE. If there is no match it returns #N/A.

    Wrapping this in ISNUMBER() means that if there is a matching appointment date, then the expression is true, otherwise false.

    I applied a pivot table to the results and filtered the results where the Match Column is True. I don't have a good reference for how to do pivot tables, so I suggest you look that up on line if you need to.
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 2 tables

    Is this an excel or an Access issue? My SQL solution is for Access as you have posted in the Access Forum. If this is excel issue, then a Power Query solution is probably the fastest method.

  7. #7
    Registered User
    Join Date
    08-06-2018
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Re: Compare 2 tables

    Thanks all for the replies - I thought to initially post this to the Excel forum, but then I began playing around with tables in access to get the answer. So I wasn't sure!

    dflak's post helped resolve, thank you.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,381

    Re: Compare 2 tables

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Compare Tables
    By jlara0687 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2018, 11:58 AM
  2. two tables compare and fix
    By Dineth in forum Excel General
    Replies: 2
    Last Post: 09-01-2015, 12:25 PM
  3. Compare two tables
    By mlint in forum Excel General
    Replies: 5
    Last Post: 10-29-2014, 08:35 AM
  4. Compare between 2 tables
    By dispro14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2014, 03:38 AM
  5. compare two tables
    By ekoot in forum Excel General
    Replies: 2
    Last Post: 02-08-2010, 07:44 AM
  6. How to compare tables.
    By tompogo in forum Excel General
    Replies: 7
    Last Post: 01-11-2010, 03:52 PM
  7. need to compare between 2 tables
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2006, 06:45 AM

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