Sometimes, you may need to sanitize your data before sharing your file. The below macro would sanitize the data and since this is completely based on random generation, hence, it can not be recreated. Make sure that you have a copy of the Excel sheet before you sanitize or mask or anonymize the data by…
Author: Vijay A. Verma
Article 47 – New Functions in Excel 2016
This is a guest post contributed by Hannah Sharron of http://spreadsheeto.com/ Microsoft has added six new built-in functions with the release of Excel 2016. These functions are also available in Office 365. In this article, we will take a quick look at three of those new functions. TEXTJOIN For a long time, the CONCATENATE has…
Challenge 64 – Sum up the Range where a particular alphabet appears
Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone. To check your answer, the sum will be 27 for above. The file related to this challenge can be downloaded from Challenge 64 – Sum up the Range where a…
Solution – Challenge 63 – Convert to Date Format
Below is a possible solution to the Challenge 63 – Convert to Date Format Put following formula and drag down =IFERROR(–SUBSTITUTE(A1,",",""),–SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,",","")," ","*",2),"*",", "))
Article 46 – Creating Pivot Table with Dynamic Range
The file related to this article can be downloaded from Dynamic Pivot Tables We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it…
Tips & Tricks 161 – When is Thanksgiving Day in a Year
Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November. Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.
VBA – A Function to Check whether a Given Number is Prime
Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648. First function…
Solution 62 – Produce the Sum for Merged Cells Headers
Below is a possible solution for the challenge – Challenge 62 – Produce the Sum for Merged Cells Headers Put following formula B14 and drag right and down =SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))
Challenge 63 – Convert to Date Format
This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format. The challenge file can be downloaded from Challenge 63 – Convert to Date Format The solution to the above challenge will be published…
Tips & Tricks 160 – When is Labor Day in a Given Year
While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September. Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day =CEILING(DATE(A1,9,1)-2,7)+2 Note – This utilizes the knowledge gained in Article 34…