by Crispo Mwangi | Jan 22, 2023 | ADVANCED EXCEL
Surendra Reddy on Linked posed a challenge on how to Clean & Split Dirty Data as shown below There are many ways of doing this but I find it easy to use below Power Query M code let Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],...
by Crispo Mwangi | Dec 2, 2022 | ADVANCED EXCEL
Here are 4 ways to Initialize the First and/or Second Name in Excel This article is inspired Bhavya Gupta Youtube Video. He elegantly solves the issue using LAMBDA function but I will try to solve solve it using normal TEXT functions. Using REPLACE & FIND Function...
by Crispo Mwangi | Sep 16, 2022 | ADVANCED EXCEL
Most lookup functions by default return either the 1st or the last match item. How do you return all matching multiple results in a lookup? How do you return all the customers who bought the product below? USING VLOOKUP TO RETURN MULTIPLE RESULTS...
by Crispo Mwangi | Aug 26, 2022 | ADVANCED EXCEL, HR
On Linkedin, after sharing my article on 7 Ways to Calculate Income tax, I got a question on how to calculate income tax in Pakistan. Below are their tax brackets USING SUMPRODUCT Step 1: Summarise the above brackets to a table like below Step 2: Use the below formula...
by Crispo Mwangi | Aug 5, 2022 | ADVANCED EXCEL
The ability to clean data is one of the most valuable skills for any data analyst. Below are 3 ways how to clean any special character from a string. Using a Formula...
by Crispo Mwangi | Jul 29, 2022 | ADVANCED EXCEL
Recently a friend approached me with the below issue. How do you compare if the applicant has all the 3 required qualifications? Using a formula =IF(SUM(ISNUMBER(SEARCH($G$3:$G$5,C3))+0)=3,”Y”,”N”) How the formula works: SEARCH($G$3:$G$5,C3)...
Recent Comments