Notes de Jérôme Prudent
Notes publiques ...

« Notes

Advent of code 2022 day1

Créée le 11 Sep 2023

  • This year I decided to improve my skills with spreadsheets. Those tools have been around for decades, whole companies have been built with it, but I don’t know how to use them. I’m sure I will learn tons of stuff along the way.
  • I’ll be using Google Sheets because it’s web based and kind of free. Maybe I’ll learn more about Google products. I’ve heard it’s way less powerful than Excel but let’s start with this tool.
  • First thing is to get the input. Empty lines matters so I got to use paste specials > values only to get my input in a single column with each elf separated by an empty row.
  • I searched for a while how I could group or split or whatever that’s called each elf’s items.
  • I searched how I could give some id to each group but couldn’t find anything
  • The web if full of shitty tutorials that explains basic manipulations, but not advanced ones.
  • I messed around, managed to compute (formulas!!!) the sum of items carried by each elves, but it’s pointless without finding a way to do a group-by. image.png
  • TIL: A formula can be pasted in a column. All cell reference will adapt to current row! For instance the formula =B2*(C1+A2) copied from row 2 can be pasted on all rows n and the formula will be =Bn*(Cn-1+An). So you don’t have to write generic formulas, the tool will adapt the formula to the context it’s pasted in. It’s a big mental shift from traditional programming languages.
  • Well, well, I had to write a script (back in comfort zone!) image.png but that was sloooow as hellllll. I tried to refactor with acquiring a single range at the start but that was not the problem. Took ~10 minutes to fill the id column ❤️‍🔥
  • The scripting language is Javascript. Cell indexing starts at 1. So you have to deal with a mix of 0 based (js) and 1 based (sheet) indexing. Accidental complexity ….
  • Wait a minute, swearing, I don’t need this script, the answer is just the =MAX(C:C)! Yep 🌟
  • I spent a great amount of time writing that slow and useless script. Beginners always try to find crazy solutions to simple problems. I think that’s because I felt overwhelmed and I thought I needed a “real” programming language, another tool, I was comfortable with.
  • Actually, that id column I computed with a script was of great help. I managed to extract each sum of items per elf with =QUERY(A:D,"Select max(C),D group by D")
  • Then I copied the values in another column sorted it and summed the first 3 rows. 🌟
  • image.png This work is not generic. I got the solution by automating some stuff, but the artefact is not really reusable. If you give me your input I’ll have to paste it, to rerun the script that gives id. The spreadsheet helped me to compute the result, faster than with pen and paper (this statement needs a benchmark…). Here is the link