Challenge No. 84: Change Pascal Case to Snake Case.
Solved using: Python in Excel.
SUBSTITUTE
Replaces text in a string based on a specific match in Excel.
Data Cleaning! Part 1
Challenge No. 98: In the Question table, historical sales values are provided in a single cell, including the Date, Product Name, and Quantity, but in a disorganized order.
Solved using:Excel (FILTER, HSTACK, IF), Google Sheets, Power Query (List.Transform, Text.Contains, Text.Split), Python, Python in Excel, and R.
Check Word Anagram Pair
Challenge No. 50: An anagram is a word or phrase formed by rearranging the letters of a different word or phrase, using all the original letters exactly once.
Solved using: Power Query.
Split and Sum a Text String Using a Criteria
Challenge No. 10: Sum only quantities above 100
Strictly use the Legacy array function or Power Query.
Solved using: Power Query.
Clean Data and Restructure Table
Challenge No. 8: (Excel formulas also welcome)
Clean the data and generate the result table as shown.
Solved using: Power Query (Table.AddColumn, Table.TransformColumns).
List Names with Two Words
Challenge No. 10: Provide a formula to list all names in A2:A20 which has two words only.
Solved using: Python in Excel.
Multiple Text Replacement!
Challenge No. 47: The “Question” table presents a list of product IDs collected from several warehouses.
Solved using:Excel (INDEX, LAMBDA, LET), Power Query (Text.Replace), Python, Python in Excel, and R.
Cross Selling!
Challenge No. 40: In online markets, when customers add items to their carts, other products, known as complementary products, which are often purchased with the selected items, are suggested to boost sales.
Solved using:Excel (ARRAYTOTEXT, FILTER, HSTACK), Power Query (List.Transform, Text.Split, Table.AddColumn), and R.
Find The Length Of The Largest
Challenge No. 9: repetition of a specific pattern!
Identify the longest continuous repetition of the
pattern “+ – -” and “+ -” in the question table for each product.
Solved using:Excel, Power Query, and R.