Array formula to combine 2 numbers into a matrix

Long time lurker, first time poster!

I have a working spreadsheet, but I'm needing to scale it up x100 examples, and I have concerns about my data processing. I figure I need to build an array formula that will process this, but can show an example of the problem.

1. I have a combination of 2 vial sizes. We'll say 17 and 21. I am looking for all combinations of these 2 numbers (up to a point). So in my calculations worksheet, I have a matrix created that multiplies varying combinations of each. See the attached screenshot.

snip2.PNG


2. I then collect the entire matrix of numbers and have it sorted into non-repeating values that are ordered from smallest to largest using the SMALL fx.

The challenge I have is that I actually do this currently with 3 different numbers to combine, and I do it out to 30 multiples of each. I've built a multi-winged matrix to calculate all 2700 cells. It works great and seemingly has no significant effect upon the performance of ONE of these. However, I'm soon to need to scale this up to at least 100 different worksheets in the same workbook. And this calculation won't be the only thing running in the workbook. I am happy that at least only one worksheet would be updated at a time, so recalculation probably wouldn't be as bad as initial opening. However, I'd love to be a great steward of the CPU cycles, in addition to just plain elegance in a worksheet.

I'll be the first to admit that Excel's matrix functions don't seem to do the math I'm looking for. However, I can't imagine a world where this type of basic permutational calculation cannot be accomplished within a single formula. If possible, I'd love to incorporate all the way into the SMALL function so that essentially the input is only Vial1 and Vial 2 and Vial 3, and there are hardcoded multipliers of each, and the output is a multi-cell array sorting from smallest to largest result. Unfortunately, I'm really blanking on my options of how to calculate this all within a hidden array, much less output in order.

Anybody with any thoughts, or maybe rabbit holes I could jump down to learn?

Thank you for all the help over the years!
Kyle

hrlngrv,

Thank you so much for the reply :-)

I haven't yet had time to parse the code to determine the reasoning for each step. However, it does seem to function as desired. And I can use the SMALL function associated with the row in order to get this sorted in realtime. Thanks! I'll see what kind of a performance hit this makes when multiplied so many times, but I believe this is self contained enough to at least reduce the number of cells getting updated.

I think you are right about the permutation math as well. However, I can't for the life of me figure out how to visualize the additional missing values. I did at least find another missing 30x30 grid that needed to exist, and thus brought the number up to 3600, as it became essentially a 60x60 grid at that point. I need sleep and caffeine before I look at that again.

Thank you kindly!
Kyle

Both of you were very effective at finding a solution to my question. amazing!

I will say, kvsrinivasamurthy's solution was something I was readily able to understand. I made some defined names and helper cells to limit the calculation hit to the CPU, and then scaled this up to 3 different prime numbers.

1. I defined some names: MultiCol and MultiRow are essentially for building arrays of {0,1,2,3,4,5,6,7,8,9,10} and {0;1;2;3;4;5;6;7;8;9;10}, that could be scaled up to 30 if needed.
2. To set my vial multiply matrix to calculate ONCE rather than with each cell, I defined the name ARRAY with 17*MultiCol + 21*MultiRow.
3. I then fed ARRAY into kvsrinivasamurthy's formula. It seems that SMALL might also have been able to have performed the same function as AGGREGATE, though perhaps with the side effect of eventually erroring if you have more cells than numbers.
4. The result was 100 total combinations of the 2 vials. I saved these all under a defined name of SortedA.
5. Because SortedA was a vertical list, I created again the same AGGREGATE function but using an array fed by SortedA + 29*MultiCol to create the next matrix that incorporates the additional vial size of 29.
6. By the time I dragged the formula down 1000 cells, it was clear there were a ton of duplicate and triplicate answers. So I used SMALL + COUNTIF in the next column over to dynamically sort without any duplicates, leaving me with only 352 unique permutations in perfect order smallest to largest!

I attempted to recalculate by changing the vial sizes, and the entire sheet updates at the same time what seems like immediately on my midrange laptop.

I still haven't determined whether or not I'll keep helper cells or attempt to do some more of this via defined names. I understand there can be some memory leaks as you scale up defined names into huge formulas. However, I hope there is some balance between the reduced recalculation needs of a huge array and the memory issues with defined functions.

Thank you each so much for your contributions. I'm looking forward to seeing what it is like when I scale these up to 100 sheets, as I don't expect them to all recalculate at the same time, but wonder about opening and saving times.

Have a marvelous evening! You both earned massive rep points and street cred!

Kyle

See attached!

Originally Posted by kvsrinivasamurthy

What you want to get from this

=IFERROR(AGGREGATE(15,6,SortedA+29*MultiCol,ROWS($D$9:$D9)),"")

The initial ask was for permutations of 2 numbers. The actual need was to make combination of 3 numbers, but I didn't want to get too complicated for me to understand the formula and process. Once I understood how the formula works, it looks like it will only find intersections in a 2d matrix. So the 2d matrix is an intermediary step, feeding another 2d matrix that is vial size 29 multiplied 0-10 and added to the 100 results from the first calculation, yielding 1000 results.

I expect the first 2d matrix sorted to be sufficient for 95% of my 100 medications, and so will likely program IF() to determine whether to create a subsequent step that greatly increases the computing requirement.