General Discussions
Discuss any topics that are not product-specific here.
First challenge where I created several "this approach doesn't work" containers. Those were on my work computer, so this is the cleaned up and working version
First part was very easy. Love the Between function!!
For the second part, I realized that generate rows would take too long (mistake made solving part 1), so figured I'd have to create groupings of the numbers
First approach, I used multi-row to find the first number in a range, and the last number. But that wound up not working. User error and zero energy to debug
Figured what was really important was the ending value of the group, so for all numbers that roll into the same grouping just got a group number. End number updated row by row. Min start number on the group, and max end number. Difference + 1 and then na grand sum.
Oh, I was lucky I remembered this from a few AoCs ago...
In Part One I simply split the data into fresh ranges and available ingredients, fed the available ingredients into a batch macro using the ranges as control parameters:
I then simply used a formula to be overwritten by each range and determined if each available ingredient was within those ranges ([Ingredients] >= [Fresh Ingredients 1] and [Ingredients] <= [Fresh Ingredients 2]).
For Part Two I was very lucky that I remembered this very puzzle from AoCs of the past. I immediately knew the generate rows was going to liquify my PC and I remembered the method to this was to simply create overlapping batches, so if you sort ascending by the lower end of the batch, then by the top end you get to a position where you can determine the bottom end of a batch and the top end of the batch below, thus creating a continuous number until there is no overlap, whereby the batch starts anew.
To achieve this I spent far too long getting a simple multi-row formula to work because I have that brain thing.
The first is as follows:
IF [RecordID]=1 THEN [Fresh ID 2]
ELSEIF [Fresh ID 2]<=[Row-1:Max Fresh ID 2] THEN [Row-1:Max Fresh ID 2]
ELSE [Fresh ID 2]
ENDIF
This basically determines if top end of the current row is below the one above, which means it's subsumed by the previous batch, or if it is higher, so take the current row max. This is important as the next step will use this to determine the "batch" into which it fits:
if [RecordID]=1 then 1 elseif
[Fresh ID 1]<=[Row-1:Max Fresh ID 2] THEN [Row-1:Batch]
ELSE [Row-1:Batch]+1
ENDIF
So, if the top of current row was selected it moves into the next batch, and if not it is part of the batch above.
For example, in the following ranges you can see the first range and the second range do not overlap:
This means the top of the range for each remains the same and they belong in different batches.
However, in the following example you can see that row 7 at least partially overlaps with row 6:
...and so rows 6 and 7 are the same batch.
Once I had this it was a simple task of grouping each batch and taking the min start point and max end point giving me a full range of numbers. One minus the other gives me the amount of different numbers (plus one...yes, it took me longer than I'd care to admit to missing that), then sum the amount.
