Challenge No. 87: The question tables provide product price lists on various dates and transaction records.
Solved using:Excel (FILTER, LAMBDA, LOOKUP), Power Query (Table.AddColumn, Table.SelectRows), Python, Python in Excel, R, and VBA.
By Difficulty
Challenges categorized by difficulty level.
Extract last group of digits from string
Challenge No. 34: Extract the last group of numbers from a given string.
Solved using: Python in Excel.
Flexible Result Table Creation
Challenge No. 11: (Excel formulas also welcome)
Generate Result table as shown.
Solved using: Power Query (List.Distinct, Table.AddColumn, Table.Pivot).
Generate A to Z letter sequence
Challenge No. 33: Provide a formula to generate the sequence shown (A till Z only).
Solved using: Power Query (List.Transform).
List Local Temperature Peaks
Challenge No. 32: Provide a formula to list the dates when temperature has been greater than previous day and next day both.
Solved using: Power Query (Table.AddColumn, Table.SelectRows).
Knn Missing Value!
Challenge No. 86: K-nearest neighbors (KNN) is a simple technique for replacing missing values with the average of the nearest values.
Solved using:Excel, Power Query, and VBA.
Repeated Letters in Cities
Challenge No. 30: Provide a formula to list the alphabets repeated in given cities.
Solved using: Power Query.
Validate ISBN-10 Numbers
Challenge No. 29: List all books from A2:A10 which have a valid ISBN-10 number.
Solved using: Python in Excel.
Dynamic Result Table Generation
Challenge No. 10: (Excel formulas also welcome)
Generate Result table as shown.
Solved using: Power Query (Table.AddColumn, Table.SelectRows, Table.Sort).
Find Nth Two Against Letters
Challenge No. 28: Considering only alphabets in column A2:A20, find the alphabet corresponding to 5th 2 in column B.
Solved using: Power Query (Table.SelectRows).