• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Max( based on criteria )

Max Alejandro Molina

Max Alejandro Molina

Member
Hello Everyone I have been trying to find a formula that can do the job no I have had no success yet, the problem is that I want to find out when the item will be received taking into account the date on column 3 and the single item ordered in column 2 and the distribution center state in column 1 (there are duplicates) I have already spent countless hours trying to find the solution. Can anybody please help me out?


Regards
 

Attachments

  • Proyecto impo.xlsx
    11.8 KB · Views: 6
There is an issue with the formula you provided, or actually i did not explain well, for instance:

Guadalajara 200 18/07/2014 19/07/2014
Guadalajara 0 19/07/2014 19/07/2014

There the formula shows 19/07 because is the latest but i need it to show the lasted where the product was actually ordered which is 18/07 , do you get what I am trying to explain?
 
Somendra, sorry to be bothering you again but i thought that with that formula i was going to be able to build up a final answer for the real problem but i just could not. The real problem is that I have an inventories file that i download from a share folder every each week, this file says as you can see in the orange column from the attached file the item number, and i wanted to find out a formula that could be dragged down and show me the the most further into the future date of the order that is bigger than 0. This info would be gotten from the DataBase wroksheet. Our restriction is that the database info structure cannot be changed at all. Do you think you can help me with this issue Somendra?
 

Attachments

  • Formula.xlsx
    56.1 KB · Views: 5
Hello Somendra,

I have just done it manual for distribution center Mexico please see example and dates manually entered, please see comment on cell "N4". And the formula I need would distinguish for different distributions centers and also the three different items and if the ordered is not >0 then i would take the last order that is bigger than 0 just like the solution you had previously given me.

Let me know if this is still unclear, please and thanks you in advance!
 

Attachments

  • Formula.xlsx
    58 KB · Views: 1
See the file. Hope this will meet your requirement. Note Mexico on both sheet are not exactly same, for formula to work they have to be same.

Regards,
 

Attachments

  • Formula (1).xlsx
    69.2 KB · Views: 3
Somendra, I have another question... I want to know if it is possible to take another date columns into account and get the newest/last day related to the item/distribution center, please see attached. I have done it manually already and i have included comments.
 

Attachments

  • Formula.xlsx
    69.2 KB · Views: 7
Hello Max,

May be this Array Formula in N3, then copy down.

=IFERROR(LARGE(IF(DataBase!D$4:D$113=K3,IF(INDEX(DataBase!F$4:H$113,,MATCH(C3,DataBase!F$3:H$3,0))<>0,DataBase!I$4:M$113)),1),"")
 
Hello haseeb, thanks for the help!!! although there is one more thing I could not get... How do I know from which specific column the= large function is taking the date from? could it be possible that if it is taking it from column I:I the date output says: for instance 27/07/2014.PoSentDate , if from column J:J for instance 27/07/2014.AtBoarderDate, if from column K:K for instance 27/07/2014.ApptRequestDate, if from column L:L for instance 27/07/2014.RealApptDate, and if from column M:M for instance 27/07/2014.DeliveredAtCEDIS ???
 
You must log in or register to reply here.
Back
Top