+ Reply to Thread
Results 1 to 3 of 3

Need simple formula that produces non-duplicate number representing pairs of time.

  1. #1
    Registered User
    Join Date
    06-03-2016
    Location
    Idaho USA
    MS-Off Ver
    2003
    Posts
    2

    Need simple formula that produces non-duplicate number representing pairs of time.

    What formula could define a number derived from a pair of start stop times that will never be same as derived from any other pair of start stop times. I've wondered if SQRT(ATime)+SQRT(BTime) would do it?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Need simple formula that produces non-duplicate number representing pairs of time.

    Hi, welcome to teh forum

    How about...
    =countifs($A$1:A1,A1,$B$1:B1,B1)
    copied down
    where A would be start time and B would be end time
    This will generate a progressive number for each duplicate match

    If that doesnt work for you, consider uploading a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-03-2016
    Location
    Idaho USA
    MS-Off Ver
    2003
    Posts
    2

    Re: Need simple formula that produces non-duplicate number representing pairs of time.

    Thank you FDibbins for the welcome and quick reply? In the attached you'll see that rows 2,12, and 22, columns I through O have a formula that is intended to produce a unique number specific to the time pairs that produced it.
    The formulas in rows 12 and 22 don't work. Does the formula in row 2 work? It adds the squareroots of the two times (start and stop) associated with that shift. I don't care what formula produces the unique number, but I just don't want a formula that could produce the same number from a different pair of times. As you see I use the number to mark with "1" every shift. If they appear on the same line then they are duplicate shifts on different days of the week, reflected in the freq. column, which I reference elsewhere to produce a recommendation for another program. For example, you'll notice all start/stop pairs are unique except the 9 am to 10 am pairs on Tuesday and Thursday
    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. [SOLVED] Highlight cells that are duplicate pairs
    By erob223 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2015, 10:46 AM
  2. Simple polynomial produces error ONLY for a certain input, otherwise works
    By anna86 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-15-2014, 01:57 PM
  3. [SOLVED] Combining a simple formula with text concatenation produces to many decimal points.
    By twelvepointelk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-18-2014, 11:13 PM
  4. [SOLVED] inputting a number representing "Time" spent on a "Task" under drop menu's
    By cubbymonkey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-09-2014, 04:03 PM
  5. excel formula produces duplicate last names
    By johnmerlino in forum Excel General
    Replies: 5
    Last Post: 11-04-2010, 12:17 AM
  6. Replies: 2
    Last Post: 02-23-2006, 07:46 PM

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