To Number Required

[Naresh Yadav]

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

 

[bosco_yip]

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)))

 

[Naresh Yadav]

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

 

[shrivallabha]

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.

 

[Peter Bartholomew]

Pretty much the same I think but with structured references and XLOOKUP.

 

[Naresh Yadav]

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