Looking to get a dynamic unique list based on date

[Gunblade]

Hi all, A simple request if using a formula, which I am, but unfortunately the formula I'm using for that:

Please Login or Register  to view this content.

Is incredibly slow given the rather obvious large size of the thing unfortunately (and it'll only continue to grow), and of course at the moment isn't all that dynamic.

So currently looking for a macro which takes in the date and lists all the unique data within that range and preferably at a click of a button once date range have been assigned.

Example data attached (And of course data is cut down with data from 2015-18 removed :p) with other formulas to show what it is I'm trying to accomplish as a whole and why the whole thing might be slow with a large data set.

From "raw data" get unique "Part Number" by date range assigned in "ACL" and copy down in "ACL" (Bonus points for alphanumeric order), then separate formula to calculate the average age of each part number but only key part is getting unique list that isn't current slow formula.

Secondary would be to also list, along with date criteria (so multi criteria macro), the part numbers where sales type is applicable (See "ACL" sheet) but that is not a necessity.

Thanks for any advice.

Edit:

Updated with new macro where I now generate a sorted unique list when activated.

Please Login or Register  to view this content.

If anyone has ideas though on how to also filter between sales type it would be appreciated.
Example file is updated.

[Pete_UK]

Another way to get those unique part numbers, which doesn't involve using an array formula, would be to put this formula in cell K2 of the Raw Data sheet:

=IF(AND(I2>=ACL!$E$2,I2<=ACL!$G$2,COUNTIF(C$2:C2,C2)=1),MAX(K$1:K1)+1,"-")

then copy this down to the bottom of your data. Then you can use this formula in A3 of the ACL sheet:

=IFERROR(INDEX('Raw Data'!$C:$C,MATCH(ROWS($1:1),'Raw Data'!$K:$K,0)),"")

Copy this down until you start to get blanks.

I suspect the formula in B3, though, with 6 SUMPRODUCT functions is also contributing to the sluggishness of the workbook - perhaps you could think about using SUMIFS and COUNTIFS as replacements.

Hope this helps.

Pete

[Gunblade]

Thanks for the info.
Though this is being used by others the less they have to deal with the better, hence a single macro button
Also 6 sumproducts condensed into 2 instead due to some silly nonsense from my part, so still a little bit slow but much more efficient for the moment.
Updated example file.
After some fiddling, it's solved XD

[Pete_UK]

Glad you got it solved - thanks for the rep.

Pete