R Solution for Excel Puzzles

Puzzles no. 364–368

Puzzles

Author: ExcelBI

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

Puzzle #364

In this puzzle we have several numbers to check. But what is the condition those numbers should met? Number should be like this:
1st digit -> 2nd digit = 1st digit +1 or 1st digit-1 -> 3rd digit = 2nd digit +2 or 2nd digit-2 and so on. So absolute value of difference between consecutive digits should increase by 1. Let’s check it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "B1:B6")

Transformation

check_seq_diff = function(x) {
  digits = as.character(strsplit(as.character(x), "")[[1]])
  diffs = map2_dbl(digits[-length(digits)], 
                   digits[-1], 
                   ~abs(as.numeric(.x) - as.numeric(.y)))
  all(diffs == 1:length(diffs))
}

result = input %>%
  mutate(test = map_lgl(Number, check_seq_diff)) %>%
  filter(test) %>%
  select(-test)

Validation

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

Puzzle #365

Second puzzle was not really hard, but certainly interesting. We need to check if there is any possibility that one of digits in number is a sum of all the others. Logically it should be the largest digits. Lets find out.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "A1:A10")
test  = read_excel("Excel/365 One digit is Equal to Sum of other Digits.xlsx", range = "B1:B5")

Transformation

evaluate = function(number) {
  digits = as.numeric(unlist(strsplit(as.character(number), "")))
  check  = purrr::map_lgl(digits, ~ .x == sum(digits[-which(digits == .x)]))
  return(any(check))
}

result = input %>%
  mutate(eval = map_lgl(Number, evaluate)) %>%
  filter(eval) %>%
  select(`Answer Expected` = Number)

Validation

identical(result, test)
# [1] TRUE

Puzzle #366

Today we get some weirdly mixed string of letters and digits. We need to make little bit more in order. To do it we need to break it into digits and letters and then treat those vectors as zip fastener: take one element from first, then second, and first again and so on. Lets zip it up!

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "A1:A10")
test  = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "B1:B10")

Transformation

zip_string = function(string) {
  letters = str_extract_all(string, "[a-zA-Z]")[[1]]
  digits = str_extract_all(string, "[0-9]")[[1]]
  
  vec_diff = abs(length(letters) - length(digits))
  if (vec_diff > 0) {
    if (length(letters) > length(digits)) {
      digits = c(digits, rep("", vec_diff))
    } else {
      letters = c(letters, rep("", vec_diff))
    }
  }
  result = map2_chr(letters, digits, function(x, y) paste0(x, y)) %>% paste0(collapse = "")

  return(result)
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Words, zip_string)) %>%
  select(-Words)

Validation

identical(result, test)
# [1] TRUE

Puzzle #367

This puzzle reminded me “Sea Battle” game. We get table with sentence and index. We have to check if index is pointing to first letter of any word in sentence. If this can be confirm, just take word, like we shot vessels in “Sea Battle”.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/367 Extract the Word Starting at an Index.xlsx", range = "A1:B10")
test  = read_excel("Excel/367 Extract the Word Starting at an Index.xlsx", range = "C1:C10")

Transform

extract_word_by_index = function(sentence, index) {
  word_pos = str_locate_all(sentence, "\\w+") %>% as.data.frame()
  word = word_pos %>% filter(start == index) 
  
  if (nrow(word) == 0) {
    word = NA_character_
  } else {
    word = sentence %>% str_sub(start = word$start, end = word$end)
  }
  
  return(word)
}

result = input %>% 
  mutate(`Answer Expected` = map2_chr(Sentence, Index, extract_word_by_index))

Validation

identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] FALSE
# Mistake in the puzzle 

input %>% bind_cols(test) %>% bind_cols(result$`Answer Expected`)

Puzzle #368

This puzzle brought many memories. When I was teenager and young adult, all “comunicational” revolution happened. Firstly those old-style mobiles, then more inteligent models, and finally smartphones come up in less then 10 years (at least in Poland). And we were texting a lot on those. If somebody get fluent in texting using multitapping, then it is possible in many different ways (below table, in pocket, behind back).
But get back to puzzle. We need to encode given words into multitap cypher.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/368 Multi Tap Cipher.xlsx", range = "A1:A10")
test  = read_excel("Excel/368 Multi Tap Cipher.xlsx", range = "B1:B10")

Transformation

encode = function(word) {
  chars = str_split(word, "")[[1]]
  pos = match(chars, letters)
  tibble = tibble(
    Letter = chars,
    Position = pos,
    Button = calculate_button(pos),
    Taps = calculate_taps(pos),
    repetitions = (map2_chr(Button, Taps, ~ rep(.x, .y) %>% paste0(collapse = "")))
  ) %>%
    pull(repetitions) %>%
    str_c(collapse = "")
}

calculate_button <- function(letter_pos) {
  case_when(
    letter_pos <= 15 ~ ((letter_pos - 1) %/% 3) + 2,
    letter_pos <= 19 ~ 7,
    letter_pos <= 22 ~ 8,
    TRUE ~ 9
  )
}

calculate_taps <- function(letter_pos) {
  case_when(
    letter_pos <= 15 ~ ((letter_pos - 1) %% 3) + 1,
    letter_pos <= 19 ~ ((letter_pos - 16) %% 4) + 1,
    letter_pos <= 22 ~ ((letter_pos - 20) %% 3) + 1,
    TRUE ~ ((letter_pos - 23) %% 4) + 1
  )
}

result = input %>%
  mutate(`Answer Expected` = map_chr(Words, encode))

Validation

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

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.

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.