+ Reply to Thread
Results 1 to 24 of 24

Monthly summary party wise from a range and duplicate do want to make one qty

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Monthly summary party wise from a range and duplicate do want to make one qty

    Dear Sir,

    Need help for monthly revenue party wise and weekly trips duplicated DO count in one qty.

    auto formula need in report sheet.

    Thanks and regards
    Dackson.
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Removed!!...........
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    In N4, copied down:

    =COUNTIF(ENTRY!$C$3:$ER$20,REPORT!M4)

    In O4, copied down:
    =SUMIF(ENTRY!$C$3:$ER$20,M4,ENTRY!$G$3:$EV$20)

    if this is NOT whjat you want, please explain more clearly...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    sr no and party name also need to come automatically from entry sheet, please fill highlighted area

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    How is this derived .."WEEKLY DUPLICATED DO QTY" ?

  6. #6
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    We are preparing delivery order (DO) for each vehicle trip, normally each trip against each delivery order (DO) will be generated But some cases vehicle have weekly committed trips, for that weekly trips preparing one Delivery Order (DO) only and all dates in between the weekly DO. For example 1st, 2nd, 3rd days 3 trips are generated but DO no A1582 same only, that mean DO no A1582 weekly DO. All weekly DO date will be last trip date, for this case 3rd was last trip. So DO date has been 3rd only. In this case 1st and 2nd day DO quantity doesn’t want to count in “NET DO QTY”, only 3rd day will add in Net DO QTY. i hope you understand.
    DAckson.

  7. #7
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    for sample of weekly Delivery Order (DO)
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty


  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    please explain the smiley's. I don't understand the meaning of it.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  10. #10
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Just pop up for viewing alert

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    you did not answer #5.

    You get better result on your question if you add a small excel file, without confidential information.

    Please also add the expected results manualy in your file.

  12. #12
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Yes i will do for clear for you. Pls wait...

  13. #13
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Pls find the attachment for more clarity. Of #5
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    you have the formula in N4 and O4

    Why do you need the answers in D4 ad E4 as well?

    Probably I don't understand the question.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    I have looked at this several times, as have others.

    No-one seems to know what you want. remove ALL the JUNK from your sheet. your report sheet has 26 columns!!! REMOVE ALL THAT ARE NOT REQUIRED. I have NO IDEA what you want. So, Explain what you want and show your expected results.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    My understanding is as follows:

    DO=Delivery Order

    A delivery order can apply to more than one day and in this case we subtract 1 from "On Jobs Trips" on the start day until the penultimate day when such a delivery order expires.

    e.g. A1582 applies to Days 1 to 3 so we subtract 1 from "On Jobs Trips" for Days 1 and 2 and 0 on Day 3.

    And there may be more than one "multiple" DO starting and finishing on the same days e.g BK2121/CF3305 start on Day 14 and both finish on Day 17 so we subtract 2 from "On Jobs Trips" for days 14,15 and 16 and 0 on Day 17.

    I assume it is possible to have the same start day but different finish day (or vice versa) when there are 2 (or more) "multiples"..

    .... so we have look through 30/31 days of data, find the "multiple" DOs, determine their start/Finish days and then do the calculations above.

  17. #17
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Yes you are right!!!!!!!!!!!!
    We want to count how many duplication do nos is generated that treat one qty only. Do duplicated days not count in net do qty, only count treat last day of duplicated do no.

  18. #18
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Dear Sir,
    Important point I want to get count of Delivery Order No. one example for your awareness. I have 18 vehicles the 1st day 18 trips executed against that 18 trips 18 Delivery order no also generated. Then 1st day was 18 trips and 18 delivery order nos. This same 2nd day 18 trips executed against that 18 trips 18 Delivery order no also generated. Then 2nd day was 18 trips and 18 delivery order nos. This same 3rd day 18 trips executed against that 18 trips 18 Delivery order no also generated. Then 3rd day was 18 trips and 18 delivery order nos. Altogether in 3 days trips are executed 1st day 18 trips + 2nd day 18 trips + 3rd day 18 trips =54 trips and Altogether in 3 days Delivery Order No are generated 1st day 18 Delivery Order No + 2nd day 18 Delivery Order no + 3rd day 18 Delivery Order No =54 Delivery Order No. I hope up to here everybody can understand. Now starting the logic condition, in example 3 days 54 trips and 54 delivery order No are generated. but please find from above attached excel file, the veh 15 have same Delivery order no In 1st , 2nd & 3rd days which is colored (find 17th row). That means the vehicle trips was 54 but count of Delivery Order No actually 52 only. We are ignoring 1st day and 2nd day Delivery Order No quantity due to the same DO no (auto formula required in I3:I33 ) but 3rd day is last day of this same DO no, So we are counting in Delivery Order No quantity. Which days ignoring that day reducing from veh trips, then I will get that concern day Delivery order no count.
    How many duplication DO no are generated from the range of C3:EV20 and how many time each DO no are duplicated that I want to reduce from the daily wise veh trips in each day.
    DAY : VEH TRIPS : IGNORE DUPLICATED DO QTY : DELIVERY ORDER NO QTY
    1 18 – 1 =17
    2 18 – 1 =17
    3 18 – 0 =17

  19. #19
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    SORRY IGNORE DUPLICATED DO QTY (auto formula required in I4:I33 )


    SR # FORMULA NEED FROM B4 COLUMN AND PARTY NAME FORMULA NEED FROM C4 COLUMN

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    Do you not have answer to this in post #3?

  21. #21
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    In N4, copied down:

    =COUNTIF(ENTRY!$C$3:$ER$20,REPORT!M4)

    N4 FORMULA NEED IN D4, WANT TO FILTER ON JOB VEH ONLY. PARTIALLY ITS WORKING FINE


    In O4, copied down:
    =SUMIF(ENTRY!$C$3:$ER$20,M4,ENTRY!$G$3:$EV$20)

    O4 FORMULA NEED IN E4, ITS WORKING FINE

    BUT i NEED IN B4 INDEX FROM THE RANGE OF C3:EV20, THE INCREMENT COUNT OF PARTY NAME WITH OUT DUPLICATION.

    AND I NEED IN C4 INDEX FROM THE RANGE OF C3:EV20, THE PARTY NAME WITH OUT DUPLICATION.

  22. #22
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    HI,
    Glenn Kennedy/oeldere /JohnTopley
    ANSWER NEED BADLY SIR, PLEASE UPDATE FOR ME THE STATUS…
    Regards,
    Dackson.............

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    ....N4 FORMULA NEED IN D4, WANT TO FILTER ON JOB VEH ONLY. PARTIALLY ITS WORKING FINE
    How as you require "filtering" by party in C4.


    ...THE INCREMENT COUNT OF PARTY NAME
    Does this mean you want count of (for example) number of PARTY 1 in the range?

  24. #24
    Registered User
    Join Date
    10-22-2014
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    68

    Re: Monthly summary party wise from a range and duplicate do want to make one qty

    yes ... filter On Job in "vehicle status" column, from all range C3:EV20 that DO count required.

    how many party in on job the range C3:EV20 that count 1,2,3,4,6, etc .. avoid duplication of party list.

+ 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. [SOLVED] need to arrange column wise data to row wise with monthly wise
    By alok.gupta4ever in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-03-2016, 11:11 AM
  2. VBA to Create weekly & Monthly wise summary with change shown in another sheet.
    By KumarPK in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 02-05-2016, 04:17 AM
  3. [SOLVED] Cumulative monthly count needed for department wise
    By kittu55 in forum Excel General
    Replies: 3
    Last Post: 08-19-2015, 08:47 PM
  4. vba code for automating branch wise day wise amount to summary file
    By pranithpm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2014, 04:18 AM
  5. Replies: 2
    Last Post: 11-03-2013, 03:49 PM
  6. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  7. [SOLVED] name wise Text summary
    By Charlie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2006, 04:45 AM

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