Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Interval between repeated events?

    OK: column A contains a growing list of dates, at weekly intervals, about 5 instances of the same date every week.
    Column B is a growing list of numbers, many of which repeat from time to time.
    (To be specific, they're church hymn numbers used each week.)
    In column C I want a formula returning a message like, "Last used x, y and z weeks ago" where the corresponding number in column B has indeed occurred previously, else "Not used before".
    I can't figure out how to do this.
    (The crude way is to filter on the number and read out the dates, but I'm after something smarter, if possible.)
    Thanks in advance!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,564
    Thanks
    384
    Thanked 1,480 Times in 1,346 Posts
    John,

    First you should change your userid to NOT include your email as you're just asking to get spammed!

    Why not just use a Pivot Table which will give you a quick visual indication of the usage of hyms.

    JohnPIvot.JPG

    Sample File: JohnPivot.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,733
    Thanks
    126
    Thanked 686 Times in 623 Posts
    John,

    You can use a User Defined Function (UDF) to perform the calculations. In my example, in cell D1, ether the formula:

    =Hymns($B$1:$B$17) where $B$1:$B$17 is the range of hymns then copy down.

    HTH,
    Maud

    DateDiff2.png

    In a standard module, enter the code:
    Code:
    Public Function Hymns(rng As Range) As String
    Application.Volatile
    '-------------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell_1 As Range, cell As Range, str As String, Row As Integer
    Row = Application.Caller.Row
    str = "Last used: "
    '-------------------------------------
    'FIND NUMBER OF WEEKS AGO
    For Each cell In rng
        If cell = Cells(Row, 2) Then
            str = str & DateDiff("ww", cell.Offset(0, -1), Date) & ", "
        End If
    Next cell
    '-------------------------------------
    'REMOVE TRAILING COMMA A RETURN WEEKS
    str = Left(str, Len(str) - 2)
    Hymns = str & " week(s) ago."
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-10 at 05:56.

  4. #4
    New Lounger
    Join Date
    Jun 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both VERY much!

    (RetiredGeek - point taken about email addy - but not intuitive to me how to change it?)

    JRR

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,048
    Thanks
    145
    Thanked 543 Times in 518 Posts
    Hi JRR

    ..what you do is browse to this site, and Register again.
    For your User Name, enter something like JRR, or Thorns, or anything you like.
    Then fill in the rest of the registration details.

    zeddy

Posting Permissions

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