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

Lot number traceability - Help Required

A

ansh

New Member
Hi

In the attached sample I am subtracting quantities from C3 , from required quantities in G3.

What I want , If I am subtracting 30, 3 times from C3. Is there any method of tracing that from which Lot. I have picked the quantity despite of how many times in H3 .
Actually I want to have , tracking of materials on First In First Out basis.Please help me to get out of this.
 

Attachments

Try this formula way.

1] In "Balance Quantity" D3, formula copy down :

=IF(C3>0,C3-SUMIF(H:H,B3,G:G),"")

2] In "Name of Lot Used" H3, formula copy down :

=IF(G3>0,INDEX(B$3:B$9,MATCH(SUM(G$3:G3),INDEX(SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,0))),"")

Regards
Bosco

Regards
Bosco
 

Attachments

Last edited:
bosco_yip said:
Try this formula way.

1] In "Balance Quantity" D3, formula copy down :

=IF(C3>0,C3-SUMIF(H:H,B3,G:G),"")

2] In "Name of Lot Used" H3, formula copy down :

=IF(G3>0,INDEX(B$3:B$9,MATCH(SUM(G$3:G3),INDEX(SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,0))),"")

Regards
Bosco

Regards
Bosco
Click to expand...

Wow Bosco! That is quite a formula. Can you do some kind of forensic analysis on it to help describe how it works?

I've got the if(G3>0 part....I am more concerned the "then" part of the equation. The nested Index, Match, Sum, Index, SumIf, Offset is losing me, and I cannot follow the logic. Please help me understand it.

Thanks, because....
Im_Offset
 
Im_Offset said:
Wow Bosco! That is quite a formula. Can you do some kind of forensic analysis on it to help describe how it works?...........
Im_Offset
Click to expand...

1] The H3 formula can be shortened to use LOOKUP instead of INDEX+MATCH and easier to understand :

=IF(G3>0,LOOKUP(SUM(G$3:G3),SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,B$3:B$9),"")

2] The formula main part is :

SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1

It give a cumulative amount in respect of range C3:C9

>>

{0;100;250;300;300;300;300;300}+1.....(the plus 1 in suit with the Lookup Range)

>>

{1;101;251;301;301;301;301;301}

3] So that, formula in H3 :

=LOOKUP(SUM(G$3:G3),SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,B$3:B$9)

=LOOKUP(30,{1;101;251;301;301;301;301;301},{"SNQA156";"SNQA157";"SNQA158";0;0;0;0})

="SNQA156"

Regards
Bosco
 
Last edited:
bosco_yip said:
1] The H3 formula can be shortened to use LOOKUP instead of INDEX+MATCH and easier to understand :

=IF(G3>0,LOOKUP(SUM(G$3:G3),SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,B$3:B$9),"")

2] The formula main part is :

SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1

It give a cumulative amount in respect of range C3:C9

>>

{0;100;250;300;300;300;300;300}+1.....(the plus 1 in suit with the Lookup Range)

>>

{1;101;251;301;301;301;301;301}

3] So that, formula in H3 :

=LOOKUP(SUM(G$3:G3),SUMIF(OFFSET(C$2,,,ROW($2:$9)-1),"<>")+1,B$3:B$9)

=LOOKUP(30,{1;101;251;301;301;301;301;301},{"SNQA156";"SNQA157";"SNQA158";0;0;0;0})

="SNQA156"

Regards
Bosco
Click to expand...
ok. I'm almost understanding it...Let me ask a few more things:
1. The offset. It looks like it is saying: start at cell c$2, don't move any rows or columns away, and use a height of row($2:$9)-1. Why do you subtract 1?
2. The sumif. How does using the criteria "<>" work exactly? My understanding is that means "not equal to". Do that make the sumif go ahead and sum all the range values in the array?

Thanks!
 
Hi
Thanks Bosco.This helped me a lot.But if I have two outwards then will this formula work in a single inward.I have tried but getting same lot number twice.Please suggest.
 

Attachments

ansh said:
Hi
Thanks Bosco.This helped me a lot.But if I have two outwards then will this formula work in a single inward.I have tried but getting same lot number twice.Please suggest.
Click to expand...

In fact, I don't understood what are the results you asking for ?

However, please follow the following steps in solve your 2nd query.

1] Remove your ineffective formula.

2] Redo the Output table together with 2 outwards and the expected result as well.

3] Re-attached the file, so someone may understand your question and give you a better solution.

Regards
Bosco
 
Last edited:
ansh said:
Hi
Its working but with a error.Lot number changes automatically.
Please find attached file.
Click to expand...

1] You made changing with the formula in cell D3, which is difference from my formula posted on Post # 10.

2] Maybe I misunderstanding your specification and unable reach with your expected, then please ignore all my posted proposals.

Regards
 
Actually I was trying to resolve the issue, but in Post 10, if we put all your formulas the issue is still there.Please have a look in attached file.

Regards
 

Attachments

You must log in or register to reply here.
Back
Top