Section III solved more formally. This solves the Transposition Cipher using an iterative macro wrapped in a loop controller macro, and doesn't rely on specific values that need to be known ahead of time. The decoder outputs over 3,000 potential solutions, which is small enough to scan with the human eye and spot the solution. For a larger input, we could easily scan for "contains actual words" or some such in the filter, rather than filtering for the known decoded text. As it happens, 4 different settings of the variables I used in the decoding algorithm yielded the right answer. This suggests optimizations may be possible. But for now I'm good... on to Section IV!
Might be one of my favorites so far! Lots of offline thinking to try to streamline a solution that would also work with any length keyword2. Here is my solution to Sections 1 & 2.
To create the decoding wheel, tokenize again, join in the Vigenere wheel created above, and then rebuild the decoding wheel with String functions. I split the Vigenere wheel text into 2 pieces. The first piece has where the first letter of the keyword starts, and continues to the end of the Vigenere string, and the second contains all of the other letters. Accomplished using FindString([VigenereWheel], [keyword2]) to find the starting position of the match, and Substring functions to find the first part of the string and the remaining text. For the first part, look into the VigenereWheel, start at the Position (that FindString function above) and continue until the end of the Vigenere string. Then the second hald was another Substring, starting at position 0, and continuing to position calculated by the FindString function. Joined those together with a simple concatenation.
Now onto solving. Another Regex tokenize to split the encoded text into columns, then add an ordering field to rebuild everything to the proper order. Then the part to make the length of the keyword dynamic. According to the pages read, the data needs to be chunked according to the length of the keyword, to relate the encoded text to the correct line to decode. So here, I figure out the number of characters in the keyword string (let's call it x), and use a MultiRow formula tool to number the rows from 1 to x.
Attached is the workflow.
Will continue to work on Section III
Great responses!!! Too many to select one as a solution. I gathered my favorite bits and pieces from your solutions and came up with the following for Sections I-III:
This is a REALLY AMAZING community!
What a fun challenge! Based on all the great learnings from the lead up challenges, section I and II were quite doable. Then came section III... I did something, but it seems like a bit of an easy way out. Curious to see what better way others came up with.