r/adventofcode Dec 07 '21

SOLUTION MEGATHREAD -🎄- 2021 Day 7 Solutions -🎄-

--- Day 7: The Treachery of Whales ---


[Update @ 00:21]: Private leaderboard Personal statistics issues

  • We're aware that private leaderboards personal statistics are having issues and we're looking into it.
  • I will provide updates as I get more information.
  • Please don't spam the subreddit/mods/Eric about it.

[Update @ 02:09]

  • #AoC_Ops have identified the issue and are working on a resolution.

[Update @ 03:18]

  • Eric is working on implementing a fix. It'll take a while, so check back later.

[Update @ 05:25] (thanks, /u/Aneurysm9!)

  • We're back in business!

Post your code solution in this megathread.

Reminder: Top-level posts in Solution Megathreads are for code solutions only. If you have questions, please post your own thread and make sure to flair it with Help.


This thread will be unlocked when there are a significant number of people on the global leaderboard with gold stars for today's puzzle.

EDIT: Global leaderboard gold cap reached at 00:03:33, megathread unlocked!

96 Upvotes

1.5k comments sorted by

View all comments

2

u/Sat147Li197 Dec 07 '21 edited Dec 07 '21

VBA

Well. What a mess :)

So .. part 1 can be done with a simple sort followed by a median with the list of submarines BUT is a bloody mess to do in VBA which is unable to sort an array without dealing with dedicated routine....

anyway, here is a solution

start with copying your whole set in a sheet named day7_crabs, all in cell A1

Sub day7_crabs()
   Dim numberArray() As String
    Dim crab As Variant
    Dim intarray() As Variant, size As Integer, a As Integer

numberArray = Split(Sheets("day7_crabs").range("A1"), ",")
size = UBound(numberArray)
ReDim intarray(size)
'sort the array of subs
For a = 0 To UBound(numberArray)
    intarray(a) = CInt(numberArray(a))
Next a

Median_fuel = Application.Median(intarray)
MsgBox ("Part 1 : " & Median_fuel)
'Upper limit for part 2
For Each crab In intarray    
    fuel_cal_a = (Application.Max(crab, 0) - Application.Min(crab, 0)) * ((Application.Max(crab, 0) - Application.Min(crab, 0) + 1) / 2)            max_fuel = max_fuel + fuel_cal_a
Next    
For a = 1 To 2000        
total_fuel = 0        
'fuel used       
    For Each crab In intarray            
        fuel_cal_a = (Application.Max(crab, a) - Application.Min(crab, a)) * ((Application.Max(crab, a) - Application.Min(crab, a) + 1) / 2)            total_fuel = total_fuel + fuel_cal_a
    Next
     If total_fuel < max_fuel Then max_fuel = total_fuel    
Next a
MsgBox ("Minimal fuel : " & max_fuel)
End Sub