Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Mulitple Conditionals

    I am new to the forum however I have having excel issues. I have 3 columns 1st column has a list of doctors with alot of duplicates. second column has date/time for surgery that coincides with the doctor. I need the 3rd column to return true of false. I need true to return if it is that doctors first surgery for that particular day. and false if not.

    Col 1 Col2 Col 3

    Gray 7/1/2011 12:00
    Blue 7/1/2011 11:00
    Gray 7/1/2011 11:00
    Gray 7/3/2011 12:00
    Red 7/2/2011 11:00
    Red 7/3/2011 12:00
    Blue 7/3/2011 1:00


    Thanks for your help

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    May I suggest that your data field for the time should be formatted to show AM or PM, otherwise you won't know if a surgery occurred at 1:00 AM or 1:00 PM. After you format the time you can figure out the correct order for each surgeon within each date.

    Now the difficult part-can the list be sorted? If not, then my opinion is that VBA code is needed to examine the activity for each name listed to identify the surgeon's first surgery for the day.

    I formatted your time field, sorted the list by surgeon, date, time, added a couple more lines to test my formula, and came up with this:

    Doctor Date Time First?
    Blue 7/1/2011 11:00 AM FIRST
    Blue 7/3/2011 1:00 AM FIRST
    Gray 7/1/2011 11:00 AM FIRST
    Gray 7/1/2011 12:00 PM
    Gray 7/1/2011 1:30 PM
    Gray 7/3/2011 12:00 PM FIRST
    Red 7/2/2011 11:00 AM FIRST
    Red 7/3/2011 12:00 PM FIRST

    Code:
    =IF((A2&B2=A3&B3)*(A2<>A1)+((A2&B2<>A1&B1)*(A2&B2<>A3&B3)),"FIRST","")
    Formula interpretation: (If name AND date EQUALS name AND date in the row below, AND name DOES NOT EQUAL name above) OR (name AND date DOES NOT EQUAL name above AND name AND date DOES NOT EQUAL name below) THEN "FIRST", OTHERWISE ""

    The formula can be shortened a bit. In a sorted list if the name does not equal the name above, then this would be the first surgery. The revised code:
    Code:
    =IF((A2<>A1)+((A2&B2<>A1&B1)*(A2&B2<>A3&B3)),"FIRST","")
    Last edited by JerryDal; 10-03-11 at 19:01. Reason: code revision

Tags for this Thread

Posting Permissions

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