• 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.

To Number Required

Naresh Yadav

Active Member
Respected All,

I Have a Excel sheet of Pick list Order Details which consist the Details of TO number, Article and respective Qty against To and Article number
Based on the Excel or Pick list my assign people go and get the article and Qty and keep it one carton
Now I want a formula that can allow me to get the TO number based on the Articles scanning in my scanning Template
kindly find the Excel attachment along with the required format




regards
Naresh
 

Attachments

  • Order Details.xlsx
    10.1 KB · Views: 8
Try,

In J4, formula copied down :

=INDEX(LOOKUP(ROW(INDIRECT("1:"&SUM($C$3:$C$11))),SUMIF(OFFSET($C$2,,,ROW($1:$12),),"<>")+1,$A$3:$A$11),AGGREGATE(15,6,ROW(INDIRECT("1:"&SUM($C$3:$C$11)))/(LOOKUP(ROW(INDIRECT("1:"&SUM($C$3:$C$11))),SUMIF(OFFSET($C$2,,,ROW($1:$12),),"<>")+1,$B$3:$B$11)=G4),COUNTIF(G$4:G4,G4)))

72953
 
Respected Bosco Sir,

Sorry for Late Reply and thank you so much for giving your time to solve my query
your Suggested formula giving me the same result which was required to me but I have to scratch my head a lot to understand
thank you so much once again sir

regards
Naresh
 
If you are okay with helper columns then simpler approach can be used.

Helper#1 Calculate cumulative running total for pick orders
=SUMIF($B$3:B3,B3,$C$3:C3)
Helper#2 Calculate cumulative running total for scanning template
=SUMIF($G$4:G4,G4,$H$4:H4)
Helper#3 Based on Helper#2 Qty identify the Pick order basket using Helper#1
=MIN(IF($B$3:$B$11=G4,IF($D$3:$D$11>=K4,$D$3:$D$11,SUM($H$4:$H$25)),SUM($H$4:$H$25)))
Finally use a LOOKUP to get the reference Pick Order basket.
=LOOKUP(2,1/(G4&L4=$B$3:$B$11&$D$3:$D$11),$A$3:$A$11)

See attached file for demonstration.
 

Attachments

  • Order Details_Help Column Solution.xlsx
    13.1 KB · Views: 3
Thank you so much Shrivallabha & Peter Sir
I am getting the same result which is required to me based on your advised Formula

regards
Naresh
 
Back
Top