Does the path taken can be behave like snake game? U turns? Up-down?
I tried with define steps from 0,0, then calculate minimum sum needed to get to next step from previous, so going through the grid with a downward diagonal line (like Dijkstra), but answer is a bit off.
This works for part 1 in under 10 seconds, we'll see how long part 2 takes, assuming it finishes (I'm currently at 8 minutes and it slows down the farther it goes...so it may take hours to finish). I may need to try something different for part 2 to help it finish faster.
Super messy Macro:
Epic fail on part 2 (it may have never ended), so I reworked it for speed. I don't like it as much as my original logic, but part 2 now completes in <10 minutes. I also just relied on manually updating the number of iterations.
Iterative Macro:
Fast this was not.
it "just" took it 1003 iterations to find the lowest cost, and 1010 iterations to complete (or 27 min!)
The main wf
I didn't manage to complete Part 2 in 10 mins like @patrick_digan - mine came in at 1 hr 31 mins.
This one took 2 days to do cleanly - many different things came into play
Anyway - finally got this done - 4 days later. Algo and solution below the spoiler break:
Also - wrapped this in discrete modules to make it cleaner, and make it easier to read - and also added a utility macro that converts the shortest path to a map (like they do on the AOC website).
OK - managed (with @patrick_digan 's suggestions) to get this down to 13m13 sec - details in the spoiler
This was a tricky one to conceptualise. I built something that worked with the test data fairly quickly but my sample had more paths so some debugging was required to output the right path without overloading my computer (a tale as old as time)
Part 2 I found pretty well with my old favourite Find and Replace<3.
Only took 7 hours because i messed up the risk limit calc so lots of unnecessary paths were calculated 😞
Macro to work out paths:
Macro to make 5x5 grid
Thanks @patrick_digan
take for few days to solved part 1. On the way to part 2.
Keys learn from Patrick: when reach every point, only keep the lowest path.
for example: here have 2 paths that reach 2,2,
1,1 > 1,2 > 2,2 = 10
1,1 > 2,1 > 2,2 = 8
we only keep the 2nd as it is lowest.
by this way, it reduce Million records to max 40,000 records per iteration.
Workflow
surround location with "-" to avoid mistake in finding repeat path.
because i use contains to identity it. (like 1,10 is found in 11,10 and 1,100 etc)
steps = x + y.
mistake: forget to remove the start point value.
The Visual in excel
And macro is similar with above. but i add filter after the input of all possible macro, (base on the steps (i.e x + y) and iteration number) to reduce the times taken in join.
(i test on part 1, it reduce from 16 second to 13 second.)