Hi QV-users.
Referring to my attached Excel file I have to make some calculations. I have made a novice solution in Excel but the file turns out to be so large that my computer runs out of memory when it calculate all rows.
Hopefully you can help me with a script to solve my problems:
1: Time spent in each line (EndDate-StartDate). If EndDate is empty then SysDate-StartDate. So I can sum time spent in each department. If the Status is BER or HBER then the time spent should be set to 0 (zero) from the StartDate with one of those Status and the following rows, e.g. in Order1_SubNo1 the time should be calculatede for StatusRowNo 1 and 2 but row 3 and 4 should be set to 0 (zero).
2: A complete view to see if an order is OK in all status rows, e.g. Order2_SubNo1 is the only order with OK in all status rows. If it's OK then the field value should be "OK" else "Fail".
3: Time spent from StartDate in StatusRowNo 1 to the StartDate where HBER or BER is shown as status for the first time. If they are not shown then the StartDate in StatusRowNo 1 to the EndDate in the last StatusRowNo or if not exist then to SysDate. This should be done for all OrderNo and SubNo.
4: A view to see which department fails an order, e.g. if department D fails in Order2_SubNo2 then this department should be "credited" as FailDepartment for all OrderNo_SubNo. It doesn't matter if another department fails later on, e.g. department A in Order2_SubNo2, then the FailDepartment is still department D.
Results should be:
Time per order:
OrderNo1_SubNo1 = 26 days
OrderNo2_SubNo1 = 56 days
OrderNo2_SubNo2 = 14 days
Time per department:
A = 44
B = 0
C = 52
D = 0
Time per FailDepartment:
A = 0
B = 26
C = 0
D = 14
Best regards,
Johnni
