Calculating Max and Minimum Dates from a Column Array

**bernhardjoshuabudion** · 05-28-2016, 06:42 PM

Hi there,

I am trying to write a macro to lookup a reference code in column A (Worksheet 1), then find the min date in another worksheet (Worksheet 2) and return that value into a column in the original worksheet.

I need to do the same for the max value as well. I have this working with array formulas manually, but performance is an issue as there are over 10,000 rows and the column array is column 12 - 91.

An example:

Worksheet 1

Reference Code Min Date Max Date
100.500.SP1 21/2/2016 13/3/2016

Worksheet 2

Reference Code Gate 1 Gate 2 Gate N...
100.500.SP1 21/2/2016 22/2/2016 ....

My current array formulas are:

MIN
=IF(MIN(IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!N:N))=0,"",MIN(IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!N:N)))

MAX
IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!AL:AL),IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!AP:AP),IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!AT:AT),IF('3 - Tagged_Item_Register_Export'!A:A=A2,'3 - Tagged_Item_Register_Export'!AX:AX)))

**alansidman** · 05-28-2016, 09:55 PM

crossposted: http://www.mrexcel.com/forum/excel-q...umn-array.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**TMS** · 05-28-2016, 10:30 PM

Performance WILL be an issue if you use full column ranges in an Array Formula, particularly if it is repeated 10,000 times.

Either convert your data to a Structured Table and use Table references, or use Dynamic Named Ranges for each of the columns, or use a Worksheet Change event handler to apply the array formulae on an as required basis.

**bernhardjoshuabudion** · 05-28-2016, 10:32 PM

@alansidman
Apologies. Won't happen again.

@TMS
Thanks for the tips. I'll use the dynamic named range idea

**TMS** · 05-28-2016, 10:37 PM

You're welcome.

Calculating Max and Minimum Dates from a Column Array

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Max and Minimum Dates from a Column Array

Re: Calculating Max and Minimum Dates from a Column Array

Re: Calculating Max and Minimum Dates from a Column Array

Re: Calculating Max and Minimum Dates from a Column Array

Re: Calculating Max and Minimum Dates from a Column Array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract Minimum and maximum dates against values in adjucent column

[SOLVED] return of row and column header for a minimum value af an array

return of row and column header for a minimum value af an array

Formula for Consolidating Array Values from a Column & Calculating Corresponding Cells

Excel 2007 : Calculating orders in one column based on dates in a second column

Sorting Dates Column and calculating MTD, YTD

[SOLVED] calculating the minimum value ignoring o

Bookmarks

Bookmarks

Posting Permissions