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,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 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,637
    Thanks
    115
    Thanked 650 Times in 592 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
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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
  •