Challenge No. 190: Write a formula to convert texts into numbers.
Solved using:Excel, Google Sheets, Power Query, Python, R, and VBA.
Python Automation
Automating tasks with Python
Column Combining! Part 1
Challenge No. 189: Given multiple columns, concatenate them based on a predefined pattern.
Solved using:Excel (BYROW, CONCAT, IF), Google Sheets, Power Query (List.ReplaceMatchingItems, List.Transform, List.Zip), Python, Python in Excel, and R.
Matrix Calculation! Part 1
Challenge No. 188: Create a formula that, for any given value of ‘n’, generates an ‘n x n’ identity matrix where all values are 0, except for the diagonal (primary) values, which should be 1.
Solved using:Excel (IF, LAMBDA, LET), Google Sheets, Power Query (List.Transform), Python, and Python in Excel.
Custom Grouping! Part 17
Challenge No. 187: Group the rows until encountering a missing date.
Solved using:Excel (DROP, GROUPBY, IF), Google Sheets, Power Query (Table.Group, Table.AddColumn, Table.AddIndexColumn), Python, Python in Excel, and R.
Column Splitting! Part 4
Challenge No. 186: Split the ID into two columns:
The first column should contain all vowels (A, E, I, O, U).
Solved using:Excel (BYROW, CONCAT, HSTACK), Google Sheets, Power Query (Text.Combine, Text.Remove, Text.Select), Python, Python in Excel, and R.
Replace Consecutive X!
Challenge No. 185: In the ID column, Replace all instances of “X” if it appears consecutively more than once with just an “X”.
Solved using:Excel (DROP, HSTACK, IF), Google Sheets, Power Query (Text.Combine, Table.Group, Table.TransformColumns), Python, Python in Excel, and R.
Match Dates!
Challenge No. 183: In the question table, the dates are provided in various formats.
Solved using:Excel (IFERROR, INDEX, LAMBDA), Google Sheets, Power Query (Text.Split, Table.Distinct, Table.TransformColumns), Python, Python in Excel, and R.
Add Index Column! Part 6
Challenge No. 182: In the question table, assign an index to the blank cells, starting from 1.
Solved using:Excel (IF, LAMBDA, LET), Google Sheets, Power Query (List.Combine, List.Split, List.Transform), Python, Python in Excel, and R.
Table Transformation! Part 22
Challenge No. 181: Transform the question structure into the result structure.
Solved using:Excel (DROP, FILTER, HSTACK), Google Sheets, Power Query (List.Alternate, List.Transform, Table.Group), Python in Excel, and R.
Hierarchical Structure!
Challenge No. 180: In the question table, parents and their children with different IDs are listed.
Solved using:Excel, Google Sheets, Power Query, Python in Excel, and R.