1

I am dynamically populating an array in Excel from another array in a different sheet based on a criteria and was successful in that but now I wish to auto update the returned total based on another two arrays of input and output.

This is the IF statement I am using:

=IF('Production JP37'!D5:'Production JP37'!D600="JP37",'Production JP37'!A5:'Production JP37'!J600,"")

The arrays look something like this

Date Product Code Product Description Location Produced Batch Code QTY
25/02/2025 PSB003SLBF Strawberry Puree JP37 25051F1 100
28/02/2025 PAP001SLHF Apple Puree JP37 25078F1 50

The criteria it is collected by is the "Location Produced".

I have another two arrays for that are pretty much the same just containing two additional columns for To and From, which I am populating from another sheet of transfers based on where it was transferred to/from the IF statement I am using is.

=IF('Internal Transfer Of Goods'!E5:'Internal Transfer Of Goods'!E600="JP37",'Internal Transfer Of Goods'!A5:'Internal Transfer Of Goods'!L600,"")

The To/From arrays look like this

Date Product Code Product Description FROM TO Batch Code QTY
25/02/2025 PSB003SLBF Strawberry Puree JP37 Freezer 25051F1 30
28/02/2025 PAP001SLHF Apple Puree JP37 Freezer 25078F1 10
Date Product Code Product Description FROM TO Batch Code QTY
25/02/2025 PSB003SLBF Strawberry Puree Freezer JP37 25051F1 20
28/02/2025 PAP001SLHF Apple Puree Freezer JP37 25078F1 5

Now I with to update the first array's QTY based on if they have the same batch code.

Any help is much appreciated.

Improve this question
6
  • The term "dynamic programming" has a specfic meaning - see stackoverflow.com/tags/dynamic-programming/info. The tag "dynamic-programming" is not appropriate for this question. Commented Apr 1 at 23:05
  • The IF functions are unnecessary. A formula such as ='Production JP37'!A5:J600 can reference a range directly - no need to wrap it in IF. What does "pretty much the same" mean? Specifically, do each of the three arrays have the same number of rows and do corresponding rows of each array match in terms of the batch number (so all three of the first rows correspond to same batch number, all three of the second rows correspond to a second batch number, etc) - because that is the implication of the illustrative examples in the question. Commented Apr 2 at 13:17
  • You are right in that the if statement for the first column is probably unnecessary. "Pretty much the same" means that the arrays are the same except the second and third have an extra column to say where it was transferred to. As for how many rows each have it will vary greatly depending on what gets moved around. The rows given where just to give a visual it doesn't mean that they will all have the same amount of rows. The first array is what is current Stock On Hand, while the second and third are transfers in and out respectively. But the transfers are often not the entire batch. Commented Apr 3 at 20:59
  • Is there a limit on the number of internal transfers that can take place over the lifetime of particular batch? Commented Apr 6 at 18:46
  • Well in theory only as many transfers that would collectively total the amount produced. So in theory yes there would be a limit but it would entirely depend on how many were transferred with each transfer. Commented Apr 8 at 3:15

2 Answers 2

Reset to default
1

I'm not entirely sure I've understood the question because to me it seems like a lot of this information is redundant but I'll try and help answer the question I think you've asked. It's also very difficult to do this as I don't know the correct sheet names, column, or row numbers. My understanding is that you want to populate the QTY column of the first table with the QTY value for the same product in the other table. The code I would suggest for this is

=IFNA(index('quantity array', match('Batch Code', 'Batch Code Array', 0)), "")

In this code, the quantity array is the column of the table you are taking information FROM, and the Batch Code Array is the batch code column, again of the table you're taking information FROM. The 'Batch Code' is the individual batch code for the corresponding row of the table you are writing IN. The function will look through the table you're taking information from, find the matching batch code number to the one in the table you're writing in and take the quantity value from that same row. If the function can't find a matching Batch code, it will stay blank.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi thanks for the reply. I'll try to clarify it for you. So basically the first table is input from job sheets of product produced, and I need to keep track of where that product produced is stored in our factories/warehouses. The problem I am running into is updating the QTY based on internal transfers, which often is not the entire batch, so I need to add or subtract an amount from that products stock, from the right batch, leaving the remainder or sum thereof as stock on hand.
0

The formula below matches Batch Codes in worksheet Production JP37 to those in worksheet Internal Transfer of Goods and calculates updated the stock levels.

=LET(
    old_stock, FILTER('Production JP37'!E5:F600,'Production JP37'!D5:D600="JP37"),
    old_batch, INDEX(old_stock,,1),
    old_qty, INDEX(old_stock,,2),
    stock_in, FILTER('Internal Transfer of Goods'!F5:G600,'Internal Transfer of Goods'!E5:E600="JP37"),
    stock_in_qty, BYROW(
                    IF(
                        old_batch=TRANSPOSE(INDEX(stock_in,,1)),
                        TRANSPOSE(INDEX(stock_in,,2)),
                        0),
                    LAMBDA(row,SUM(row))
                    ),
    stock_out, FILTER('Internal Transfer of Goods'!F5:G600,'Internal Transfer of Goods'!D5:D600="JP37"),
    stock_out_qty, BYROW(
                    IF(
                        old_batch=TRANSPOSE(INDEX(stock_out,,1)),
                        TRANSPOSE(INDEX(stock_out,,2)),
                        0),
                    LAMBDA(row,SUM(row))
                    ),
    HSTACK(old_batch,old_qty+stock_in_qty-stock_out_qty)
)

In the formula:

FILTER is used instead of IF for creating arrays. This is because the IF formulation used in the question inserts a blank row into the resulting array whenever a row in a data worksheet does not contain "JP37" in the relevant column.

In addition, only the Batch Code and QTY values are extracted from the two data worksheets. This is because no other columns in these worksheets are relevant to determining the stock levels at location JP37.

old_stock is an array showing the current stock position at JP37 before accounting for any transfers, and comprises old_batch and old_qty.

stock_in is an array of stock transfers to location JP37

stock_out is an array of stock transfers from location JP37

The expressions for stock_in_qty and stock_out_qty are workarounds to the previously (and erroneously) used SUMIFS functions. The expressions allow for multiple transfers inwards and outwards of a particular batch in the calculation of the updated stock for that batch.

The result of the formula is an array showing the updated stock position.

Each of the 4 arrays (old_stock, stock_in, stock_out and the formula result) has two columns with the first containing Batch Codes and the second containing QTY.

The formula is just an array version of the standard stocks and flows equation

New Stock = Old Stock + Stock In - Stock Out

Although the formula is reasonably sound, there are some issues.

  1. Excel does not permit self-referencing formulas. For example, putting the formula =A1+2 in cell A1 results in a circular reference warning. In consequence, workbook cells containing stock levels cannot be directly updated using formulas. All that can be achieved with formulas (whether array or otherwise) is the calculation of New Stock levels which are derived, in part, from the Old Stock levels. Both must co-exist and are linked through the formula.
  2. Multiple sets of transfers require the Old Stock values to be replaced by the New Stock ones after each Stock In, Stock Out pair has been processed. Avoiding the circular reference problem when performing the replacement is tricky and contains traps for the unwary.
  3. Depending on the external data behind worksheet Production JP37 it might not contain information for Batch Codes for which there is no stock showing at location JP37. This can happen if the source system suppresses the creation of records where the stock level associated with a particular Batch Code is zero. However, if such a suppressed Batch Code is present in the Stock In data (because it is on worksheet Internal Transfer of Goods), the formula ignores it. Only codes present in Old Stock get matched and so, in this case, the stock transfer is erroneously ignored by the formula.

Issue 1 underlines why Excel is not always a suitable tool for managing data. Issues 2 and 3 can be resolved and outline approaches are sketched below.

Issue 2

The stocks and flows equation can be re-written as a pair of equations:

New Stock(n) = Old Stock(n) + Stock In(n) - Stock Out(n)

Old Stock(n+1) = New Stock(n)

This re-writing reflects an ongoing series of stock transfers, perhaps identified across daily, weekly or monthly periods and the need to replace Old Stock with New Stock after processing each Stock_In, Stock_Out pair.

The problem of circular references can be resolved making a copy of the New Stock(n) array by copying the formula's results array to a convenient location using Copy/Paste Values. This copy, which will be a worksheet range rather than an array, is then used as a lookup table to adjust the stock values in the QTY column of worksheet Production JP37. These adjusted values will be determined using a function such as VLOOKUP and, once calculated, a second Copy/Paste Values operation should be used to break the formula links between the stock levels on Production JP37 and those in New Stock(n). After this is done, Production JP37 will be consistent with Old Stock(n+1).

The formula simply uses whatever happens to be on worksheet Internal Transfer of Goods as the array equivalents of Stock In and Stock Out. Consequently, immediately after Production JP37 is updated to reflect its consistency with Old Stock(n+1), the formula will be implementing the array equivalent of equation

New Stock(n+1) = Old Stock(n+1) + Stock In(n) - Stock Out(n)

This is an incorrect equation. The Stock In and Stock Out arrays have not been updated to be Stock In(n+1) and Stock Out(n+1). In consequence the results of the formula will be showing incorrect values. This is the trap for the unwary.

Avoiding this trap will require good data management skills, so that it is clear at all times whether there is consistency between the data worksheets of Production JP37 and Internal Transfer of Goods.

Issue 3

Here, the issue can be resolved by appending to the Old Stock array (represented as old_stock in the formula) rows for which a Branch Code is in the Stock In array but not in Old Stock.

This is achieved by changing the definition of old_stock in the formula so that the array obtained from worksheet Production JP37 is renamed as old_stock_ws, the missing codes are called missing and the formula for old_stock is changed to

IF(ISERROR(missing),old_stock_ws,VSTACK(old_stock_ws,HSTACK(missing,0)))

where missing has formula

FILTER(INDEX(stock_in,,1),ISNA(MATCH(INDEX(stock_in,,1),INDEX(old_stock_ws,,1),0)))

Improve this answer

4 Comments

This looks Promising I'll try get it working for my purposes. I am getting an error using the formula you gave saying that I have entered to few arguments any idea why?
The error is likely mine. I thought the SUMIFS() functions would work when all three arguments are presented as arrays. Turns out I was mistaken. Only third argument can be in array form with dynamic array versions of Excel (first two must be ranges rather than arrays). There is a workaround for this problem and I will edit my answer in next 24 hours.
Hi I am trying your revised code and it seems to mostly answer the question, there are a few kinks still like it not showing anything if it wasn't in the production list i.e. was produced elsewhere and transferred to JP37 (even if it is in the transferred in/out). And throws a CALC error if there is no stock_out transfer.
The #CALC! error occurs because the formula does not correct for the stock_in or stock_out arrays having no rows. This can be corrected by wrapping the BYROW functions in the definitions of stock_in_qty and stock_out_qty within an IFERROR function with second argument of 0. The problem of "not showing anything if it wasn't in the production list" is Issue 3. The discussion of Issue 3 suggests how this problem can be resolved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.