“Excel Puzzle Solutions: Rotated Words, Sales Ranking, Consecutive Numbers, Parasitic Numbers,

Puzzles no. 449–453

Puzzles

Author: ExcelBI

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

Puzzle #449

We are starting this week with rotating words. Wait what? No, we are not gonna swirl them and twist them. We need to find out if one word is second one’s rotated version, which means that it has to be shifted some places and letters from the end are coming to the beginning. But it is little bit tricky. Rotated version should be rotated by X spaces, where X is not equal to lenght of word. They have to be equal in lenghts as well, because it should come to word back after shifting it with rest of characters. Let’s rotate them.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/449 Rotated Strings.xlsx", range = "A1:B10") %>% arrange(String1)
test  = read_excel("Excel/449 Rotated Strings.xlsx", range = "C1:D6") %>% arrange(`Answer Expected`)
colnames(test) = colnames(input)

Transformation Approach 1

is_rotated = function(string1, string2) {
  is_0_rot = string1 == string2
  is_rot = str_detect(paste0(string1, string1), string2)
  is_length_equal = nchar(string1) == nchar(string2)
  return(is_rot & !is_0_rot & is_length_equal)
}

result = input %>%
  mutate(is_rotated = map2_lgl(String1, String2, is_rotated)) %>%
  filter(is_rotated) %>%
  select(-is_rotated)

Transformation Approach 2

result2 = input %>%
  filter(map2_lgl(String1, String2, ~str_detect(paste0(.x, .x), .y) & .x != .y & nchar(.x) == nchar(.y)))

Validation

identical(result, test)
# [1] TRUE

identical(result2, test)
# [1] TRUE  

Puzzle #450

Today we need to rank sales person in different companies without sorting structure of table itself. Fortunatelly in case of R it is pretty easy.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/450 Ranking.xlsx", range = "A1:C20")
test  = read_excel("Excel/450 Ranking.xlsx", range = "D1:D20")

Transformation

result = input %>%
  mutate(rank = dense_rank(desc(Sales)), .by = Company)

Validation

all.equal(result$rank, test$`Answer Expected`)
# [1] TRUE

Puzzle #451

We have long sequence of numbers, and we need to find which of them negative and positive separately, will form longer consecutive chains. Let’s try doing it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "A1:A20")
test  = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "D1:E3")

Transformation

result = input %>%
  mutate(group = cumsum(Numbers - lag(Numbers, default = 0) != 0),
         pos = ifelse(Numbers > 0, "P", "N")) %>%
  summarise(count = n() %>% as.numeric(), .by = c(group, Numbers, pos)) %>%
  filter(count == max(count), .by = pos) %>%
  summarise(Number = paste(unique(Numbers), collapse = ", "), Count = unique(count), .by = pos) %>%
  arrange(desc(Count)) %>%
  select(-pos)

Validation

identical(result, test)
# [1] TRUE

Puzzle #452

Why there is mosquito in ilustration? Because we have parasitic numbers today? What are they? Those are numbers that if multiplied by single digit integer number, will form number with almost the same shape but rotated (last digit comes to beginning). And today we need to find numbers that are parasitic, are lower than 1M and we need their multipliers as well. Get to work.

Loading libraries and data

library(tidyverse)
library(readxl)

test = read_excel("Excel/452 Parasitic Numbers.xlsx", range = "A1:B8")

Transformation

a = tibble(Number = as.character(1:1000000)) %>%
  mutate(cycled = str_c(str_sub(Number, -1), str_sub(Number, 1, -2)) %>%
           as.numeric() %>%
           as.character()) %>%
  filter(nchar(Number) == nchar(cycled),
         as.integer(cycled) %% as.integer(Number) == 0,
         as.integer(cycled) != as.integer(Number)) %>%
  mutate(across(everything(), as.numeric)) %>%
  mutate(Multiplier = cycled / Number) %>%
  select(-cycled)

Validation

identical(a, test)
# [1] TRUE

Puzzle #453

We need to find out from how many fruits from two lists we need to make exact pairs, so if there is 3 apples in one list, but 2 in second, there are only 2 pairs. Lets find the rest.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Excel/453 Common in Columns.xlsx", range = "A1:B12")
test  = read_excel("Excel/453 Common in Columns.xlsx", range = "D2:E6")

Transformation Approach 1

result = input %>%
  mutate(nr_l1 = row_number(), .by = List1) %>%
  mutate(nr_l2 = row_number(), .by = List2) %>%
  unite("List1", List1, nr_l1, sep = "_") %>%
  unite("List2", List2, nr_l2, sep = "_")

l1 = result$List1
l2 = result$List2

common = intersect(l1, l2)

result2 = as_tibble(common) %>%
  separate(value, c("Match", "Count"), sep = "_") %>%
  mutate(Count = as.numeric(Count)) %>%
  slice_max(Count, by = Match)

Transformation Approach 2

result = input %>%
  pivot_longer(cols = everything()) %>%
  count(value, by = name) %>%
  mutate(nr = n_distinct(by),
         min_n = min(n) %>% as.numeric(),
         .by = value) %>%
  filter(nr == 2) %>%
  select(Match = value, Count = min_n) %>%
  distinct()

Validation

identical(result2, test)
#> [1] TRUE

identical(result, test)
# [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.
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.

Continue reading: R Solution for Excel Puzzles

Insightful Analytics: Using R to Solve Excel Puzzles

The text deals with the use of R programming language to solve various complex problems typically handled with Excel. The author provides solutions for five distinct puzzles, focusing on topics such as word manipulation, sales ranking, number sequences, parasitic numbers, and list comparison. To help analysts and coders better understand these subjects, let’s analyze the long-term implications and discuss potential future developments. We’ll also offer some actionable advice.

Key Points and Long-term Implications

The article illustrates how concepts encoded within complex Excel tasks can be transferred and solved using R. This cross-compatibility could have wider implications for analysts in many business sectors, opening new avenues for data processing and automation.

With modern organizations becoming increasingly data-driven, leveraging R’s capabilities could help solve intricate problems more efficiently. The examples presented in the article demonstrate this capability and point to a potentially transformative shift. Whether it is manipulating string data, ranking sales data, processing numeric sequences, or list comparison, R’s powerful libraries appear well suited to the task.

Possible Future Developments

Given the increasing importance of data manipulation and analytics, it’s reasonable to anticipate further integration between languages like R and Excel. Such developments could unlock tremendous value for businesses, particularly those struggling with large, complex datasets.

One potential area for exploration could be the automation of these R-based solutions within Excel. Embedding R scripts into Excel might allow analysts to access the power of R without leaving their familiar environment. This interoperability between Excel and R could potentially be a significant development in the tech industry.

Actionable Advice

• Embrace the power of R: If you’re an analyst or a data science professional, consider learning R. It provides versatile functionalities that could enhance your big data handling capabilities.
• Think Integration: Find ways to integrate R and Excel in your business workflow. Combining Excel’s user-friendly interface with R’s computational abilities can lead to a highly streamlined analytical environment.
• Stay updated: Given the rapid pace of technological advancements, stay abreast of the latest developments in data analytics. Attending webinars, online courses, and industry seminars can help you remain on the cutting edge.
• Collaborate and share: The author invites interaction and the sharing of ideas for improvement. Don’t hesitate to take them up on the offer. Collaboration is a key component in the growth and development of the tech industry.

In conclusion, the rise of data-centric businesses necessitates the blending of versatile languages like R with conventional tools like Excel. The examples and solutions provided in these puzzles reflect the possibilities of such integration, underscoring the future potentials in the field of data analytics.

Read the original article