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,958
    Thanks
    422
    Thanked 1,606 Times in 1,450 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,982
    Thanks
    157
    Thanked 774 Times in 706 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 04: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,420
    Thanks
    165
    Thanked 643 Times in 611 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
  •