Advent of Code 2022 Day 5 (BaseA Style)

[AlteryxCommunityTeam]

Discussion thread for day 5 of the Advent of Code - https://adventofcode.com/2022/day/5

[mmontgomery]

Iterative macro that I spent too much time trying to organize. I had four outputs: 1 for loop, 1 for done, 1 for every iteration and 1 for every iteration as a matrix to spot check. Had to manually create a table from the input. P2 just was a sort switch from P1

Spoiler

[Jean-Balteryx]

Solution with no macro for part 1 :

Spoiler

The monster formula that allows it (and has driven me insane) :

Spoiler

IIF([Origin] = 1, IIF(Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]) = "", ".", Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number])) + Substring(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), FindString(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), " ")), REGEX_Replace(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), "((.*? ){"+ToString([Origin]-1)+"})[^ ]*(.*)", "\1"+IIF(Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]) = "", ".", Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]))+"\3"))

Part 2 is achieved by removing the ReverseString function.

[LiuZhang]

Spoiler

WorkflowIterative macropart 2 only change

As part 2 takes change of only one tool, guess I am on the right track, a bit long of flipping the rows 😅

[DavidP]

Took me hours to find one tiny bug in my iterative macro. Only got a sniff of what was going on when I started counting the overall number of crates (56) at every iteration and realized that crates were mysteriously going missing... The crate thief turned out to be the REPLACE function!!! I promptly replaced it with a SubString function.

Spoiler

[Jotigautam]

A bit late to the party! Did this one with string functions 😀

Spoiler

[MeganBowers]

Dang! This got much harder.

Spoiler

Ok I could have definitely used fewer tools for parsing but I'm proud of my iterative macro

workflow:
 

iterative macro (just eliminated the reverse string formula for the second part):

[DataG8]

I spent entirely too long trying to build this out in a matrix style solution only to revert back to stack strings.

Spoiler

 

 

 And had to refer to the interactive lessons to setup the iterative macro.

[peter_gb]

Also went for the string functions and reverse string approach. Used a batch macro to dynamically send in the move length and from and to fields to the crate moving process, the output of which had to be iterated upon, so batch within an iterative. Tons of troubleshooting necessary, lost over half my crates at one point and have lost more than half of my marbles by attempting this before work to get no stars, then an hour or two again after work for two very frustrating/satisfying stars.

Spoiler

 

[estherb47]

Went the non-macro route. 18 tools for both solutions. Muti-Row and creative use of the Replace function FTW. I'm sure there is a more elegant way to write this formula, but it works!

Spoiler

IF [RecordID]=10 THEN Replace(replace([Combined],getword([Combined],[To]-1),(left(GetWord([Combined], [From]-1),[Number]))+getword([Combined],[To]-1)),GetWord([Combined],[From]-1),right(GetWord([Combined],[From]-1),length(GetWord([Combined],[From]-1))-[Number]))
ELSE

Replace(replace([Row-1:Combined2],getword([Row-1:Combined2],[To]-1),(left(GetWord([Row-1:Combined2], [From]-1),[Number]))+getword([Row-1:Combined2],[To]-1)),GetWord([Row-1:Combined2],[From]-1),right(GetWord([Row-1:Combined2],[From]-1),length(GetWord([Row-1:Combined2],[From]-1))-[Number]))
ENDIF

[NicoleJ]

Well this one was... gross. But after solving it a gross way last night (so gross, in fact, that I will not be sharing that one to the community at large, for vanity reasons), and then letting it haunt me all day today... I finally figured out how to Tool Golf this one! 18 tools for both parts!! All that's needed is to change a True to a False and BAM! Two solutions. 😁

Spoiler

I knew there was a way this could be done with strings. Brings me back to some of the IntCode string parsing tricks from a few years ago... *shudder*

The trick to this one is to pad the strings so that I always know how many characters are in each "column"... then, with possibly the grossest Multi-Row Formula I've ever written, I was able to deconstruct and piece back together the strings, row by row, based on the instructions provided for crate moving. It still ain't pretty, BUT it doesn't require a macro, and it runs super fast!!