Dynamically updating and doing math on arrays in Excel - Stack Overflow

admin管理员组

文章数量:1352167

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

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.

• arrays
• excel
• if-statement
• dynamic
• conditional-statements

Share Improve this question Follow edited 2 days ago Jonathan Marshall asked Apr 1 at 2:47 Jonathan MarshallJonathan Marshall 1111 bronze badge New contributor Jonathan Marshall is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 3

• The term "dynamic programming" has a specfic meaning - see stackoverflow/tags/dynamic-programming/info. The tag "dynamic-programming" is not appropriate for this question. – DMM 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. – DMM 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. – Jonathan Marshall Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: 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.

本文标签： Dynamically updating and doing math on arrays in ExcelStack Overflow