Challenge No. 110: After reconciling the company’s bank transactions with the records in the financial department, discrepancies were found between 5 rows of bank transactions and 7 rows of financial records.
Solved using: Excel.
UNIQUE
Returns unique values from a range or array in Excel.
Top Words in Presidents
Challenge No. 123: Column A contains list of US presidents.
Solved using: SQL.
Lookup Uncommon Names in the set
Challenge No. 29: Extract Staff who only attended 1 training
Dynamic array function allowed, but Extra
marks for Legacy solutions or PowerQuery Solution.
Solved using: Python in Excel.
List max frequency alphabets across
Challenge No. 33: Generate the answer as shown.
Solved using: SQL.
Avg Cooperation Time!
Challenge No. 108: Calculate the average cooperation time in months for those who are still with the company (do not have value on column leave date) as of 16/08/2024, categorized by their respective levels.
Solved using:Excel (AVERAGE, FILTER, HSTACK), Google Sheets, Power Query (Date.Month, Date.Year), Python, Python in Excel, and R.
Extract Top Number Groups
Challenge No. 114: Extract the group of numbers from A2:A11 and list the top 3 unique maximum and minimum numbers.
Solved using: SQL.
Characters Repetition!
Challenge No. 105: In the question table, where some passwords are provided, extract the 6 most commonly used characters across all the passwords and count their repetitions.
Solved using:Excel (CONCAT, FILTER, GROUPBY), Google Sheets, Power Query (List.Combine, List.Distinct, List.Transform), Python, Python in Excel, and R.
Latest Date Shop Data
Challenge No. 106: Get the data for all shops corresponding to latest date as shown in the result table.
Solved using: Python in Excel.
Custom Average!
Challenge No. 103: Today is 20/08/2024, and we’re calculating the average delivery time per product in days.
Solved using:Excel (AVERAGE, CHOOSECOLS, FILTER), Google Sheets, Power Query (Table.AddColumn, Table.Group), Python, Python in Excel, and R.
Avg Cost Between City Pairs
Challenge No. 92: Find the average cost between the pair of cities.
Solved using: Python in Excel.