R Solution for Excel Puzzles

Puzzles no. 469–473

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #469

This task is suprisingly easy and interesting at once. We have sequence of letters separated with comma and space. They are not necessarily in proper order, but we need to mark them in columns with their names. For example, if string contain letter “C”, there should be C in column C in corresponding row. Let’s find out how to do it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/469 Split.xlsx", range = "A2:A9")
test  = read_excel("Excel/469 Split.xlsx", range = "C2:H9")

Transformation

result = input %>%
  mutate(Data = strsplit(as.character(Data), ", "),
         rn = row_number()) %>%
  unnest() %>%
  arrange(rn) %>%
  pivot_wider(names_from = Data, values_from = Data) %>%
  select(A, B, C, D, E, F)

Validation

identical(result, test)  
# [1] TRUE  

Puzzle #470

Tuesday’s puzzle was not really hard, because solving it seems very easy. Just make all possible permutations of digits in number, and then find first bigger than. And it was my first approach, but it was not really impressive in context of time, when I approached 9 or 10 digit numbers. It took too long. So I decided to check, if there is any algorithm to solve it, and yes there is. As I was looking at the instructions, I was not sure how to code it, but I decided to use some pretty powerful companion of many coders nowadays: ChatGPT. And I asked it how to understand algorythm in plain English. So… find out.

Find the Turning Point:
Look at the number from right to left.
Find the first digit that is smaller than the digit right next to it.
Example: In the number “534976”, start from the right:
6 is greater than 7.
7 is less than 9.
9 is greater than 4.
4 is less than 5.
So, stop at 4 because 4 is smaller than the next digit 9.

Find the Smallest Larger Digit:
Look at the digits to the right of the number you just found.
Find the smallest digit that is larger than the digit you stopped at.
In “534976”, the digits to the right of 4 are “976”.
The smallest digit larger than 4 in “976” is 6.

Swap the Digits:
Swap the digit you stopped at with the smallest larger digit you found.
After swapping 4 and 6 in “534976”, the number becomes “536974”.

Sort the Right Side:
Sort the digits to the right of where the swap happened in ascending order.
In “536974”, the digits to the right of 6 are “974”.
Sorting “974” gives “479”.
So, “536974” becomes “536479”.

Result:
The new number “536479” is the next greater number.
To sum it up, you:
Find the first dip from the right.
Find the smallest number larger than that dip.
Swap them.
Sort the remaining digits to get the next greater number.

That’s it! The next greater number for “534976” is “536479”.

So I coded it this way.

Loading libraries and data

library(tidyverse)
library(readxl)
library(gtools)

input = read_excel("Excel/469 Next Greater Number with Same Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/469 Next Greater Number with Same Digits.xlsx", range = "B1:B10")

Transformation

find_greater_from_same_digits = function(number) {
  number = as.character(number)
  n = nchar(number)
  number_splitted = as.numeric(unlist(strsplit(number, "")))

  i = n - 1
  while (i > 0) {
    if (number_splitted[i] < number_splitted[i + 1]) {
      break
    }
    i = i - 1
  }
  if (i == 0) {
    return("No such number")
  }
  j = n
  while (j > i) {
    if (number_splitted[j] > number_splitted[i]) {
      break
    }
    j = j - 1
  }
  number_splitted[c(i, j)] = number_splitted[c(j, i)]
  number_splitted = c(number_splitted[1:i], rev(number_splitted[(i + 1):n]))
  return(paste(number_splitted, collapse = ""))
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Number, find_greater_from_same_digits)) 

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE

Puzzle #471

We are often preparing coding algorythms in those challenges, but this time we have to make mechanism to decrypt encoded messages. Unfortunatelly we already used this type of cypher, and here comes good news, we can use almost all code from one of the tasks from not so far past, and make needed adjustments. That is nice example of code recycling.

Original task #377.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/471 Keyword Cipher Decrypter.xlsx", range = "A1:B10")
test  = read_excel("Excel/471 Keyword Cipher Decrypter.xlsx", range = "C1:C10")

Transformation

prepare_keycode = function(keyword) {
  keyword = str_split(keyword, "")[[1]] %>%
    unique()
  alphabet = letters
  keycode = c(keyword, alphabet[!alphabet %in% keyword])  
  return(keycode)
}

decode = function(sentence, keyword) {
  keycode = prepare_keycode(keyword)
  code = set_names(letters, keycode,)
  words = str_split(sentence, " ")[[1]]
  words = words %>%
    map(str_split, "") %>%
    map(function(x) {
      x = x %>%
        unlist() %>%
        code[.] %>%
        paste(., collapse = "")
      return(x)
    })
  sentence = paste(words, collapse = " ")
  return(sentence)
}

result = input %>%
  mutate(`Answer Expected` = map2_chr(`Encrypted Text`, Keyword, decode))

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE

Puzzle #472

That was weird idea at the beginning, but I realised that it can be solved in two ways with different amount of code, but also different literacy in R. First thing to do is to merge all row into one string containing numbers, some operator and some numbers again. Unfortunately we have to do it this way, because are in different columns. So my first solution was about split, regex and conditional expression, but there is also another one using tidy evaluation and rlang package.

Loading libraries and data

library(tidyverse)
library(readxl)
library(rebus)
library(rlang)

input = read_excel("Excel/472 Operator in a grid.xlsx", range = "A1:G10")
test  = read_excel("Excel/472 Operator in a grid.xlsx", range = "I1:I10")

Transformation — V1

pattern = START %R% capture(one_or_more(DGT)) %R% capture(NOT_DGT) %R% capture(one_or_more(DGT)) %R% END

evaluate_expression = function(n1, op, n2) {
  n1 = as.numeric(n1)
  n2 = as.numeric(n2)
  switch(op,
         "+" = n1 + n2,
         "-" = n1 - n2,
         "*" = n1 * n2,
         "/" = n1 / n2)
}

result = input %>%
  unite("result", 1:7, sep = "") %>%
  extract(col = "result", into = c("n1", "op", "n2"),  pattern) %>%
  mutate(`Answer Expected` = pmap_dbl(list(n1, op, n2), evaluate_expression))

Transformation — V2

result2 = input %>%
  unite("result", 1:7, sep = "") %>%
  mutate(`Answer Expected` = map_dbl(result, ~ eval(parse_expr(.x))))

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
#> [1] TRUE

identical(result2$`Answer Expected`, test$`Answer Expected`)
#> [1] TRUE

Puzzle #473

As you know from time to time we have something to draw and this time it is kind of ribbon with each word separated by specific signs. Just look up on the screenshot from challenge and you will now everything. As all of those challenges are desinged for Excel primarily, I always try to get as close as possible. And this time was not perfect, but similar to task. Check it out, because it will not be validated, another way than eye only.

Transformation

print_banner_matrix <- function(word) {
  n <- nchar(word)
  banner_matrix <- matrix("", nrow = 4, ncol = n * 3)
  for (i in seq_len(n)) {
    char <- substr(word, i, i)
    if (char != " ") {
      banner_matrix[1, (i - 1) * 3 + 1:3] <- c(" ", "_", " ")
      banner_matrix[2, (i - 1) * 3 + 1:3] <- c("/", " ", "\\")
      banner_matrix[3, (i - 1) * 3 + 1:3] <- c("|", char, "|")
      banner_matrix[4, (i - 1) * 3 + 1:3] <- c("\\", "_", "/")
    } else {
      banner_matrix[1, (i - 1) * 3 + 1:3] <- rep(" ", 3)
      banner_matrix[2, (i - 1) * 3 + 1:3] <- rep(" ", 3)
      banner_matrix[3, (i - 1) * 3 + 1:3] <- rep(" ", 3)
      banner_matrix[4, (i - 1) * 3 + 1:3] <- rep(" ", 3)
    }
  }
  apply(banner_matrix, 1, function(row) cat(paste(row, collapse = ""), "\n"))
}

print_banner_matrix("EXCEL")
print_banner_matrix("MICROSOFT")
print_banner_matrix("POWER QUERY")

If you haven’t noticed, my difference to original is that I have two || between letters.

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.
PS. Couple weeks ago, I started uploading on Github not only R, but also in Python. Come and check it.

R Solution for Excel Puzzles was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.