+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Code using WRONG COLUMN to group invoices by date and totals in I should be using Col F

  1. #1
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Question Code using WRONG COLUMN to group invoices by date and totals in I should be using Col F

    I'm having a problem with this code- picking up the wrong column! -- I tried to adjust some numbers - but didn't get the desired result..
    (see attachment)
    This code goes through a table of Invoices and generates a new sheet (shown in attch'd) to place those invoices in a GROUPED/Segmented way by week.
    Hoping someone can see where the adjustment needs to take place so it will use the DUE DATE (column E) as it's source when placing into segments.
    Currently it appears to be using (column D) which really jacks up the segmented report..

    The weeks run MONDAY through SUN -- therefore a DUE DATE of 6th would be placed in the first segment, 13th would be in 2nd segment, etc.
    For example, Jan of 2018's weeks would be:
    1st-7th
    8th-14th
    15th-21st
    22nd-28th
    29th-4th of Feb

    Tried changing these d's to e's? but didn't work -- appears other references need to be adjusted---
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Column I's totals SHOULD BE USING Column F when it goes to place the SUM out at right --
    Not sure if that's working properly -- seemed to be off

    Thanks greatly!
    Attached Images Attached Images

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling
    MS-Off Ver
    Microsoft Office 365
    Posts
    17,833

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Where in the code is the wrong column being referenced?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    lol -- that's what I need help with -- I don't know!? or I'm not understanding your question...
    From the results of the image posted -- the code is using the wrong column -- it is using Col D to group dates.. and SHOULD BE USING Col E
    (notice how rows 48-51 of COL E have mixed dates and are clearly not all from the same WEEK)
    So, I need the code to use E
    In other words, Col E should have the 01/03/17 invoice in a segment by itself because none of the other Col E invoice DUE dates fall within that week of 1/1-1/7.
    Next, the 2 invoices with DUE DATES of 01/13 would be grouped in the Jan 8-13th segment
    Next, the 2 invoices with DUE DATES of 01/18 would be grouped in the Jan 15-21 segment
    Next the 2 invoices with DUE DATES of 02/28 would be grouped in the Jan 22-28 segment
    Next, the 2 invoices with DUE DATES of 02/20 would be grouped in the Feb 19-25 segment
    Next, the 1 invoice with DUE DATE of 02/26 would be alone in the Feb 26-Mar 4th segment
    Last, the 1 invoice with DUE DATE of 03/05 would be alone in the Mar 5-11 segment

    (there's a lot more invoices so, this grouping is helpful to see what was DUE each week and how much income was supposed to come in that week -- vs the actual --- as well as a look-ahead of what's income is scheduled to come in on future weekly segments on various jobs...)

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling
    MS-Off Ver
    Microsoft Office 365
    Posts
    17,833

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED, scroll down and click Manage Attachments.

  5. #5
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    yes, I'll get one stripped out and get it posted shortly =-)

  6. #6
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Question Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Here's a sample file with all the code crammed into a single Module -- that takes the original large, large report and generates the tabs you see. The first weekly segmented report is the one that was generated with the code (that has the issue) --
    The invoices should be grouped by DUE DATES but the code is grouping them using the other date column..
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    10,627

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Try change to
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Woo Hoo! I had lost this code with the "before/after" somehow and search all over trying to find it-- thank you for providing it!
    I updated it to include the new names to work with the attached workbook and ran it --

    It appears to be using the proper COL E now, HOWEVER, something's wrong with the way it's calculating what to put where ---
    For example:
    It put Jan 28th and Jan 29th into the same weekly segment -- but that's not correct. (the proper week should run Monday thru Sunday)
    JAN 22 thru JAN 28 should be grouped
    JAN 29 thru FEB 4 should be next wk's group

    It put them together like this:
    IN 00011348 12/29/2017 01/28/2018 319,462.36 319,462.36
    IN 00011349 12/29/2017 01/28/2018 3,263.50 3,263.50
    IN 00011336 11/30/2017 01/29/2018 213,250.00 213,250.00

    There's a tab called: WKLY RANGES that shows the FROM TO dates for each month (all starting on Mondays and Ending on Sundays)
    If needed, I can build that tab out to accommodate a full year's worth of 52 weeks showing all the MON thru SUN start/end dates or if there's an automated way to generate that tab -- that'd be great too!
    Then the MODULE 3 code can perhaps LOOKUP (look to that tab to know the ranges) if that's helpful?

    Here's the new code I placed into Module 3: (it's using the correct date column now, but the grouping is slightly off)
    Please Login or Register  to view this content.
    Last edited by CHRISOK; 01-04-2018 at 12:29 AM. Reason: more info to see wkly ranges tab

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    10,627

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Your original thread
    https://www.excelforum.com/excel-pro...p-by-date.html

    Quote Originally Posted by CHRISOK
    (all starting on Mondays and Ending on Sundays)
    Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Question Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    awesome! thanks for link -- I had a diff issue there but it still included this code.

    Now, for the grouping of dates - still getting the issue even after changing it to Tuesday... Here's the outcome this time..
    (I pasted the date ranges table out to right to verify what's in the wrong group - the pink ones are including ones that should not be there)

    I'll start changing it to each day of the week and running it to see if I can find one that works -- if you see another way - let me know - I'll let you know if any other day works.. starting with Monday..
    Attached Images Attached Images

  11. #11
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Question Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Here's outcome when that same setting was changed to Monday (still problems in pink)

    So we've used: Sunday, Tuesday, now Monday -- I'll keep trying the other days to see what outcomes might be...
    Attached Images Attached Images

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    10,627

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Is it a same workbook that you have posted?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Question Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    No, the one you just posted has a tab wiped out (although the name is the same as the one I originally posted). The one I posted has also has 3 tabs but the Wkly Totals tab is not wiped out.
    The Wkly Totals tab is the one that gets generated after running all the Modules in order..

    I'm using the original for testing because it has the original Sheet1 report in it that gets stripped and eliminated.. the leftovers from it end up on the STRIPPED-DOWN-MASTER tab ... and from THAT tab, the final tab called: Wkly Totals is generated (where it should be taking what it sees in the STRIPPED DOWN MASTER tab and placing the rows into a Wkly segmented look..

    Newly attached is the same original file I posted above (but I've added back in the "Sheet1" tab that gets eliminated when the code is run.

    In the orig file, I threw all 4 MODULES into a single module (now named "OLD").
    To test it from scratch, DELETE the Wkly Totals tab and STRIPPED MASTER tab -- then start with MOD 1, MOD2, MOD 3 then MOD 4
    Then go to the newly generated Wkly Totals tab and see the way things got segmented..

    I just tried using "SATURDAY" as a replacement and see it's still having grouping by Due Date issues.
    So far, Sat, Sun, Mon & Tues have been tried -- I'll continue with replacing with Wed, Th, & Fri..

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    10,627

    Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Quote Originally Posted by jindon View Post
    Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    You didn't follow this change.

  15. #15
    Registered User
    Join Date
    11-03-2004
    Posts
    50

    Thumbs up Re: Code using WRONG COLUMN to group invoices by date and totals in I should be using Col

    Yes, I did -- I've tried changing it to Tues, Wed, Thur, Saturday, Sunday (originally) and FINALLY just now tried FRIDAY and FRIDAY SEEMS TO BE THE WINNER!
    Yeah!! not sure why FRIDAY works -- but I'm just happy it works! I checked every one all the way up and all were placed into the correct weekly segment!
    Here's the results of the TUES (with pink issues) vs FRI (with all yellow confirmed each is accurate!)

    THANK YOU SO MUCH FOR YOUR HELP -- getting me to the place that needed tweaking! (I'll click the scales icon to add this success to your REPUTATION)

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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