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.
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.
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)
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...
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
sr no and party name also need to come automatically from entry sheet, please fill highlighted area
How is this derived .."WEEKLY DUPLICATED DO 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.
for sample of weekly Delivery Order (DO)
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.
Just pop up for viewing alert
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.
Yes i will do for clear for you. Pls wait...
Pls find the attachment for more clarity. Of #5
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.
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.
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.
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.
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
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
Do you not have answer to this in post #3?
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.
HI,
Glenn Kennedy/oeldere /JohnTopley
ANSWER NEED BADLY SIR, PLEASE UPDATE FOR ME THE STATUS…
Regards,
Dackson.............
How as you require "filtering" by party in C4.....N4 FORMULA NEED IN D4, WANT TO FILTER ON JOB VEH ONLY. PARTIALLY ITS WORKING FINE
Does this mean you want count of (for example) number of PARTY 1 in the range?...THE INCREMENT COUNT OF PARTY NAME
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks