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

Locate Branch and quantity

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have a stock count report from four branches and one central branch.

We have worked out the difference of physical stock count for all branches.

We would like to use a function to arrive at the excess stocks from central branch and other branches to check whether the differences in each branch can be fulfilled.

Where there is no excess stocks, this will be an actual difference.

The function should capture the transfer from Branch and to branch with the quantity required to fulfill the difference stock.

Kindly guide on the correct formula to be used.

Thank you very much,

with regards,
thomas
 

Attachments

  • Transfer Stock.xlsx
    254.1 KB · Views: 7
Respected Sirs,

I tried the until now to get the branch name and the quantity result but failed.

Kindly if attached the nested if statement and the index match used to arrive at the result but was not successful.

Kindly check and let me know how to get the correct result.

Thank you very much.

with regards,
thomas
 

Attachments

  • Transfer Stock.xlsx
    254.9 KB · Views: 8
Respected Sirs,

I tried to use max and min to arrive at the differences and then matched the corresponding branch, but it is taking the first value for all results.

Kindly check and let me know is there a way to search for the correct corresponding value.

Thank you very much,

with regards,
thomas
 

Attachments

  • Transfer Stock.xlsx
    275 KB · Views: 1
Thomas Kuriakose said:
Respected Sirs,

We have a stock count report from four branches and one central branch.

We have worked out the difference of physical stock count for all branches.

We would like to use a function to arrive at the excess stocks from central branch and other branches to check whether the differences in each branch can be fulfilled.

Where there is no excess stocks, this will be an actual difference.

The function should capture the transfer from Branch and to branch with the quantity required to fulfill the difference stock.

Kindly guide on the correct formula to be used.

Thank you very much,

with regards,
thomas
Click to expand...
Hi ,

Nowhere in this entire problem description is a single worksheet tab / row / column / cell mentioned ! And this is an Excel forum !

What is so clear to you may be Greek and Latin to those who are not from the same domain.

The only thing that links so many members from a variety of domains is their love or ignorance of Excel ; when you post a problem description , as far as possible try to phrase it in Excel terminology.

1. If you want a formula , which cell or cells should have this formula ?

2. Which cells / rows / columns / worksheet tabs should this formula reference to arrive at its output ?

3. What should be the logic which the formula should implement ?

If you can give a clear problem description taking into account the above 3 points , then you should get a solution in the second post in your thread , assuming that the first post is your problem description.

Otherwise , members will either ignore the thread , or post their own questions and doubts , and days can be spent in trying to clarify matters.

Narayan
 
Respected Sirs,

Apologies for not giving the requirement correctly.

The above attached worksheet is a summary of the stock count carried out in 5 branches including the central branch.

We have entered the count summary branch wise in Sheet 1 of the transfer stock file. This includes the following headers- Material number (A2), System Stock (SYS) Cells, B2,E2,H2,K2,N2, Count (Physical count) Cells, C2,F2,I2,L2,O2, Diff - (Difference between system stock and physical count, Cells, D2,G2,J2,M2,P2. These header are created for each branch and they are named, Branch 1, 2, 3, & 4, and Central Branch.

We need to summarize the above data to get the following -

1. Check for positive stock (Difference between Physical count and system stock where physical stock is greater than system stock) and find in which branch this stock is available - Cell Q3.
2. Check for Branch which has shortage of material to fulfill no difference between system stock and physical count based on the positive stock available (Cell R3).
3.Calcualte the quantity to be transferred from positive stock branch to the branch which has shortage to fulfill the sock is matching between system and physical for the specific branch (Cell S3).

4. If there are no positive stocks in any branch to fulfill the shortage for any branch, the shortage should be treated as actual difference for the branch and sum of all the shortage should be treated as total difference (U3).

I tired using some functions from V3 to Z3 to arrive at this result, but the values selected are based on the first values searched and it is not checking the values is other cells and there is no co-relation.

I hope the requirement is now clear.

Thank you very much,

with regards,
thomas
 

Attachments

  • Transfer Stock.xlsx
    275.8 KB · Views: 4
Hi ,

Still not very clear.

If we take material ID 10001 , we have the following data :
Code: 
Branch .......... Physical Count ............ System Stock
    1                     51                       51
    2                     37                       37
    3                     25                       25
    4                      0                       37
Central                    0                       50
Based on the above data , what would appear in cell Q3 ?

Narayan
 
Respected Sir,

In the above material, there is no branch which has positive stock (physical stock greater than system stock to cover the shortage in any branch, so Q3, R3 & S3 in this case should be blank. The total difference should be -87 in U3.

For material 10051, the total positive stock is (8 (Branch 1 + 1 (Central Branch)= 9), and shortage of -1 (Branch 4). In this case Q3 will be Branch 1, R3 will be Branch 4 and S3 will be Quantity 1. The total difference will be (8+1+(-1) = 8.

Thank you very much,

with regards,
thomas
 
Respected Sir,

The Column T values are correctly calculating.

The branch from (Column Q) and to (Column S) are not getting updated in any cell.

The quantity to transfer (Column S) is not getting the correct value.

for example:
Row 32, Material 10030. The positive diff is (15(D32)+ 1(P32) and the shortage is M32 (-2).

The value for Q32 should be Branch 1,
The value for R32 should be Branch 4,
The value for S32 should be 2, it is calculating 5 currently.

Thank you very much,

with regards,
thomas
 
Respected Sir,

This is perfect. Thank you very much for the solution and your time.

Much appreciated.

Sir, one question, the header in P2 is "Diff CB Stock", do we need to change this to "Diff" as per the function and other cell names.

Thank you very much once again,

with regards,
thomas
 
Respected Sir,

Thank you very much for this information.

Sir, for one condition kindly check the results -

Row - 281
Material - 10279

The total difference in branch T281 is correct = -34

But for the transfer quantity in S281 = 7.

Branch 1 = difference = -7
Branch 4 = difference = -32

Central Branch = positive = 5

The maximum quantity that can be transferred in this case is 5 as only 5 pieces are greater than the system stock.

Thank you very much,

with regards,
thomas
 
Respected Sir,

Apologies for the late reply.

Thank you very much for the revision.

I will check and get back to you for any clarification after our half year audit.

Thank you very much once again.

with regards,
thomas
 
Respected Sir,

I checked and found this to be working perfectly.

There are some incorrect inputs for some rows whether the SYS and count values are not there and hence the result is incorrect.

Thank you very much once again,

with regards,
thomas
 
You must log in or register to reply here.
Back
Top