+ Reply to Thread
Results 1 to 18 of 18

Get date if category and month match

Hybrid View

  1. #1
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Get date if category and month match

    I have a workbook with Checking account register on sheet1, Budget on sheet2. Checking columns are Date, To/From, Category, Credit, Debit, Balance. Budget columns are Due Date, Category, Amt Due, Paid. I am trying to pull the date from checking on sheet1 and insert it in the budget column paid on sheet2, provided the category and month match on sheet1 and sheet2. Hope that made sense. Attached is the excel file. Really appreciate any and all help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    One can adjust it to the proper columns.

    Sub post()
    
    Dim ck, bg As Worksheet
    Set ck = Sheets("Checking")
    Set bg = Sheets("Budget")
    
    r = 3
    While Cells(r, 1) <> ""
    If ck.Cells(r, 8) = "" Then
    m = Month(ck.Cells(r, 1))
    ck.Cells(r, 9) = m
    ct = ck.Cells(r, 3)
    With bg.Range("b:b")
    Set c = .Find(ct, LookIn:=xlValues)
    
    If Not c Is Nothing Then
    fa = c.Address
    Do
    dt = (bg.Cells(c.Row, 1))
    dm = Month(dt)
    r2 = c.Row
    Set c = .FindNext(c)
    Loop While dm <> m And fa <> c.Address
    If dm = m Then
    bg.Cells(r2, 8) = "Paid " & Month(dt) & "/" & Day(dt)
    ck.Cells(r, 8) = "**"
    End If
    
    End If
    End With
    End If
    r = r + 1
    Wend
    
    End Sub

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,885

    Re: Get date if category and month match

    I haven't tested this code but it is difficult to read. I suggest formatting code for readability.

    Sub post()
    
       Dim ck, bg As Worksheet
       Set ck = Sheets("Checking")
       Set bg = Sheets("Budget")
       
       r = 3
       While Cells(r, 1) <> ""
       
          If ck.Cells(r, 8) = "" Then
             m = Month(ck.Cells(r, 1))
             ck.Cells(r, 9) = m
             ct = ck.Cells(r, 3)
             With bg.Range("b:b")
                Set c = .Find(ct, LookIn:=xlValues)
                If Not c Is Nothing Then
                
                   fa = c.Address
                   
                   Do
                      dt = (bg.Cells(c.Row, 1))
                      dm = Month(dt)
                      r2 = c.Row
                      Set c = .FindNext(c)
                   Loop While dm <> m And fa <> c.Address
                   
                   If dm = m Then
                      bg.Cells(r2, 8) = "Paid " & Month(dt) & "/" & Day(dt)
                      ck.Cells(r, 8) = "**"
                   End If
                   
                End If
             End With
          End If
          
          r = r + 1
          
       Wend
    
    End Sub
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    Thank you rcm and Jeff. I removed the formula from the paid column on the budget sheet and tried the code. Here is a screenshot. Added info to column H but it matches the due date, not the date paid. Let me know if there is something else I should try with the code. Thanks again. NOTE: info in column F is what I put there for reference and is the paid date from checking (no code involved).
    Capture.PNG

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    revisions: if there is already a posting for the given category it searches for the next one. If cannot find one not having a post, it sticks the payment to the last one in the category. The accumulated expenses was added as part of the testing.

    Sub post()
    
    Dim ck, bg As Worksheet
    Set ck = Sheets("Checking")
    Set bg = Sheets("Budget")
    
    r = 3
    While Cells(r, 3) <> ""
      If ck.Cells(r, 8) = "" Then
        m = Month(ck.Cells(r, 1))
        d = Day(ck.Cells(r, 1))
    '    ck.Cells(r, 9) = m
        ct = ck.Cells(r, 3)
        With bg.Range("b:b")
          Set c = .Find(ct, LookIn:=xlValues)
          If Not c Is Nothing Then
            fa = c.Address
            r2 = c.Row
            Do
              dt = (bg.Cells(c.Row, 1))
              dm = Month(dt)
              r3 = r2
              r2 = c.Row
              Set c = .FindNext(c)
            Loop While (bg.Cells(r2, 8) <> "" Or dm <> m) And fa <> c.Address
            If dm <> m Then
              r2 = r3
            End If
              bg.Cells(r2, 8) = bg.Cells(r2, 8) & ", " & m & "/" & d
              bg.Cells(r2, 9) = bg.Cells(r2, 9) + ck.Cells(r, 5)
              ck.Cells(r, 8) = "**"
            
          End If
        End With
      End If
      r = r + 1
    Wend
    
    End Sub

  6. #6
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    Thank you rcm. I sincerely appreciate your time and effort. I deleted the formulas in the budget sheet paid column and ran the new code you provided. Here is a screenshot. Results of the code are in columns H and I. It does not seem to find all the categories and dates. If there is anything I need to do please let me know. Is there a way to put the results in the paid column? Thanks again.
    Capture2.JPG

  7. #7
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    I am sending the workbook I worked on.
    There are some categories in the checking sheet that are not budgeted. What do you want to do with them?

    to run it the posting flag in the checking sheet has to be cleared (column H)
    Attached Files Attached Files
    Last edited by rcm; 04-12-2015 at 03:30 PM. Reason: more instructions

  8. #8
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    That is great rcm. Thank you. For the categories that are not budgeted, would clearing them in column H work, if they were included in the budget? Will checking the posting flag in column H be required for every budgeted item transaction in checking? Did you use vba? I did not see code as before. Trying to learn too. Thank you again! (UPDATE: code is a module. Nice!)
    Last edited by bobtjr; 04-12-2015 at 04:13 PM.

  9. #9
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    The non budgeted items could be added to list flagging them as "NOT budgeted". Hence, you could have the budgeted totals and the expensed totals in the same page.
    Now, that the routine works, would you prefer it to be automatic or activated via button?
    Last edited by rcm; 04-12-2015 at 04:32 PM. Reason: clarity

  10. #10
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    "The non budgeted items could be added to list flagging them as "NOT budgeted". Hence, you could have sum the budgeted totals and the expensed totals in the same page." Forgive me but I am not sure I follow you on this.
    I added some budget items and flagged them in checking but the paid and amount results did not show even after I ran the macro (module). Is there a work around to flagging them in checking column H? Maybe a list of budgeted items that could be named? Hope that made sense. I really appreciate your help.
    I think automatic routine would be best.

  11. #11
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    I improved on a couple of things

    1. Made it automatic if a value in the checking tab is changed (It will run through all unposted lines)

    2. Painted green and added to the budget tab all those entries that could not be found.
    Attached Files Attached Files
    Last edited by rcm; 04-12-2015 at 06:35 PM. Reason: typo

  12. #12
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    You are amazing, rcm. Thank you.
    If checking column H is ** the item is included in budget, if it is xx it is not, correct?
    The dates in budget paid column are repeating the same date(s), see screenshots.
    I entered a groceries transaction in checking and it showed in budget but when I deleted it from checking, as if it was already entered or a mistake, it did not delete from the budget worksheet. Changing the category on the budget worksheet does not change the data, even after running the macro. Adding a category to the budget sheet and changing the transaction in checking from xx to ** had no affect. I do not mean to be overly picky or seem unappreciative, rcm, because I do apprecite all your time and effort. Just wanted to put the workbook through some normal use.
    Capture3.JPG
    Capture4.JPG

  13. #13
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    1. The xx and the ** are posting flags done by the routine to get a complete run one must CLEAR column H.
    2. The redundant dates are the result of me not clearing the column D in budget before each run. Sorry.
    3. Now, the tricky part: updates can only be done through a complete rerun (with the code posted). Updating code could be written though. But it is far more easy to write a cleaning code (to clear the H column in the checking tab and the dates and values in the budget tab)

    4. I improve the routine with clearing all flags and dates and running the whole works. To make it run automatically change any value in the checking tab. If you want to change category do so but rewrite the value (thats the trigger)
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    Wow. Thank you, rcm.
    I added a category to budget and did a few transactions in checking, manually ran the macro and all seems well. I will test more later, Sunday night is family time. I had to delete a row from budget when I input the wrong date and it messed up the groceries category and result when I did (sorry). See groceries dates 3-13 and 3-28. Would there be any way to pull this data into a monthly report? Could there be an automated way to update the data? Not sure if family members will remember to run a macro (they don't know what a macro is). I apologize if I am asking too much. If I am, please let me know.
    Capture5.JPG

  15. #15
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    I did some quick reporting but if it is not what you want please feel free to send over the expected output layout at your leisure.

    One thing, the copy I sent runs the macro if the debit or credit values are changed or retyped...
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    Hello RCM, Apologies for the delay. Very hectic at work. The latest rev looks great, thank you. I have some reporting ideas that are on the excel workbook I actually use. I did not post it here for privacy reasons. I will add them to the workbook we are working on and upload it. Probably will not be until this weekend. Thanks again for all your help.

  17. #17
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Get date if category and month match

    You are welcome. Keep in touch if you need more help!!!

  18. #18
    Registered User
    Join Date
    02-08-2015
    Location
    US
    MS-Off Ver
    2010, 2013
    Posts
    9

    Re: Get date if category and month match

    Appreciate your patience, RCM. And all your help. Here is the latest from me with the budget report I am using on another worksheet. I added a sheet called Notes to the workbook where we can put ideas, questions, comments, etc.
    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. Index / Match / Small by Month with Date Criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2015, 05:37 AM
  2. [SOLVED] sumif formula for date and month with match criteria
    By alimamak in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-08-2012, 08:31 PM
  3. Calculate month to date returns, index and match
    By nickmangan in forum Excel General
    Replies: 2
    Last Post: 07-13-2012, 07:01 AM
  4. Replies: 6
    Last Post: 05-11-2012, 09:20 AM
  5. Match a Date to a Month-Year to Assign a PO#
    By cedarhill in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2009, 01:44 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