Nov 19 2021 10:34 AM
I'm currently working on a project that requires me to pull unique warehouse locations from a table, and list our different SKUs assigned to each location. Right now I'm using multiple dynamic ranges to filter and sort out the data I need and using more dynamic ranges on top of that to pull out each SKU stored in each location. The result is a ton of different dynamic ranges and calculations that are slowing the workbook to a crawl.
Is there a way to combine these arrays into one? I've tried experimenting with MMULT and a couple other methods but haven't had any luck.
The workbook is structured as follows:
First formula in AA2 is this, meant to pull a list of all locations that have a max capacity under 25% (getting low and need to be replenished.
=+FILTER(Table2[Code],(Table2[% of Max Capacity]<=25%)*(Table2[% of Max Capacity]>0%)*(Table2[Type]="Non-Fixed"))
Second for the SKU list, looks at that location and pulls out all of the individual parts stored at that location. I tried making this filter on the first dynamic range but can't get it to work properly so its individually copied down for each item.
=+TRANSPOSE(UNIQUE(FILTER(InvTable[ItemCode],InvTable[StorLocCode]=AA2)))
The formula in Z just looks up all of the parts in each location and sums demand which is pulled from another column.
=+IFERROR(SUM(XLOOKUP(AB2#,$T$2#,$U$2#)),"")
I then created my own dynamic range using the indirect and a count of how many items are in Z, and ranked the locations in order from greatest demand to least.
=+SORT(FILTER(Inventory!Z2:INDIRECT("AA"&Inventory!$Z$1),Inventory!Z2:INDIRECT("Z"&Inventory!$Z$1)>=LARGE(Inventory!Z2:INDIRECT("Z"&Inventory!$Z$1),45)),1,-1)
Is there a way to combine the dynamic ranges in AA and AB? I think it would really cut down on processing and speed up the workbook.
I can share the workbook if needed, just have to scrub it a bit first. Thank you!
Nov 19 2021 11:10 AM
I guess you may XMATCH(Locations, InvTable[StorLocCode]), return Parts by INDEX with above and combine both arrays with IF or CHOOSE. Better with sample, above is just an idea if I understood correctly your case.
Nov 19 2021 11:58 AM
Attached a scrubbed and changed up sample but all methods are the same.
I tried:
INDEX(InvTable[ItemCode],XMATCH(FILTER(Table2[Code],(Table2[% of Max Capacity]<=25%)*(Table2[% of Max Capacity]>0%)*(Table2[Type]="Non-Fixed")),InvTable[StorLocCode]))
which returns the first part of each location specified, but if there are multiple parts in one location that gets left off. Not sure how to combine the arrays with choose.
Now that I'm thinking it through, the sum that I'm doing W2 is dependent on the AB formulas being distinct dynamic ranges. I'll play around with xmatch and see if I can come up with a better method.
Thanks for your help.
Nov 19 2021 02:18 PM
SolutionSorry, I misunderstood. If we speak about "Low Non-Fixed Locations by Demand" or like, that's array of array. If modify, when with lambdas and supporting functions.
However, I'd modify a bit existing formulae. First, avoid using of INDIRECT() and define dynamic ranges with INDEX() instead. For example, W2# could be
=LET(
zRangeL, Inventory!Z2:INDEX( Z2:Z1000,Inventory!$Z$1 ),
zRange, Inventory!Z2:INDEX( Z2:Z1000,Inventory!$Z$1-1 ),
aaRange, Inventory!Z2:INDEX( AA2:AA1000, Inventory!$Z$1 - 1 ),
SORT(FILTER( aaRange, zRange >= LARGE( zRangeL, 45)),1,-1) )
Spills could be combined like in H2#
=LET(
Part, UNIQUE( FILTER( InvTable[ItemCode], InvTable[Zone Code]=J1)),
Qty, SUMIFS( InvTable[Quantity], InvTable[ItemCode], Part, InvTable[Zone Code],$J$1),
ZOne, SUMIFS( InvTable[Pallet], InvTable[ItemCode], Part, InvTable[Zone Code],$J$1),
ZTwo, SUMIFS( InvTable[Pallet],InvTable[ItemCode],Part,InvTable[Zone Code],$K$1),
Projection, XLOOKUP( Part,InvTable[ItemCode],InvTable[Projection]),
CHOOSE( {1,2,3,4,5}, Part, Qty, ZOne, ZTwo, Projection) )
Plus if no special reasons I'd don't use binary format, dynamic arrays are not optimized for it.
Please check attached.
Nov 19 2021 04:57 PM
This is EXACTLY what I was wondering was possible. Using LET() to define all of the separate ranges then combining them with CHOOSE() makes a lot sense. Looks like this helped the calculation speed a bit too.
I'll definitely start incorporating this regularly and playing around with it a bit more. I really should use index more, indirect is my go to for situations like this but index has much more flexibility.
=LET(k, SEQUENCE( ROWS($T$2#) ), IFERROR( SUM( XLOOKUP( AB2#, INDEX($T$2#,k,1), INDEX($T$2#,k,2) ) ),"") )
This is really interesting, using the sequence as your lookup and return array is not something that I would have thought possible.
Thank you so much!
Also I like the spaces after calling a function, very clean
Nov 20 2021 02:20 AM
@DKoontz , you are welcome, thank you for the feedback.
Nov 20 2021 02:04 PM
@DKoontz Hi,
> Is there a way to combine these arrays into one?
I am not sure my suggestion is what you need and since the attached file is not xlsx I cannot download it (security reasons) but to answer your question;
Yes - you have an anchor cell AA2 that can tell how many rows you need in the combined array.
You also know that demand is one column to the left (since I guess you may want to include them) and the items are one column to the right, equals width = 3 plus an unknown maximum number of items per DC/warehouse being at least one. This will catch only the first unless you change 3 to a number large enough to cover an expected maximum.
=OFFSET(AA2;0;-1;ROWS(AA2#);3)
The needed width of the combined array can be found by counting the number of items per DC;
A16 lists the unique combinations
=UNIQUE(FILTER(Table2[DC]:Table2[Code];Table2[% of Max Capacity]<=E2))
D16 visualizes the number of items per DC - this is not needed.
E16 finds the max number of items per DC
=LET(DCsWithItemsUnderLimit;INDEX(A16#;0;1);
output;MAX(COUNTIFS(DCsWithItemsUnderLimit;UNIQUE(DCsWithItemsUnderLimit)));
output
)
Using the calculated max no of items added to previous figure 3 (or change it to the actual no of columns to get a dynamic full width), the combined array including all items can look like
=LET(maxNoOfItems;E16;
anchorCell;AA2;
fullWidth;COLUMNS(Z2:AB2)-1+maxNoOfItems;
leftCol;OFFSET(anchorCell#;0;-1);
currentRegion;OFFSET(leftCol;0;0;;fullWidth);
currentRegion
)
And finally sort and delimit (if wanted)
=LET(maxNoOfItems;E16;
anchorCell;AA2;
fullWidth;2+maxNoOfItems;
leftCol;OFFSET(anchorCell#;0;-1);
currentRegion;OFFSET(leftCol;0;0;;fullWidth);
output;INDEX(SORT(currentRegion;;-1);SEQUENCE(AB23);SEQUENCE(;fullWidth));
output
)