Sat 19 Aug 2017

Downloads 17 - Sample CSV Files / Data Sets for Testing (till 1.5 Million Records) - Credit Card

By |Saturday, August 19th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real credit card data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 1500000 records. 1.5 Million records will cross 1 million limit of Excel. But 1.5 Million Records are useful for Power Query / Power Pivot. These csv files contain data in various formats like Text and Numbers which should satisfy your need for testing.

This data set can be categorized under "Credit Card" category.

The data generated follow all known rules for credit cards.

Below are the fields which appear as part of these csv files as first line.

(more…)

Tue 15 Aug 2017

Solution - Challenge 64 - Sum up the Range where a particular alphabet appears

By |Tuesday, August 15th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the Challenge 64 - Sum up the Range where a particular alphabet appears

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

 

 

Sat 12 Aug 2017

Downloads 16 - Sample CSV Files / Data Sets for Testing - Human Resources

By |Saturday, August 12th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 500000 records. These csv files contain data in various formats like Text, Numbers, Date, Time, Percentages which should satisfy your need for testing.

This data set can be categorized under "Human Resources" category.

Below are the fields which appear as part of these csv files as first line.

(more…)

Sat 05 Aug 2017

VBA - Data Masking or Anonymize the Data through a Macro

By |Saturday, August 05th, 2017|Categories: VBA|Tags: , , , , , , , , , , , , , , |0 Comments

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 running this data. As once done, the data can not be recreated.

The Excel file related to this article can be downloaded from Data Masking Macro

(more…)

Sun 30 Jul 2017

Article 47 - New Functions in Excel 2017

By |Sunday, July 30th, 2017|Categories: Articles|Tags: , , , , , , , , |0 Comments

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 been a standard for many users as a method for joining data strings together. However, with the introduction of TEXTJOIN, Microsoft had refined this process even further.

(more…)

Sat 22 Jul 2017

Excel Quiz 54

By |Saturday, July 22nd, 2017|Categories: Quizzes|Tags: , , , , |0 Comments

Excel Quiz 54 - A Quiz on Review Tab in Excel

This quiz checks your knowledge on Review tab in Excel.

Sat 15 Jul 2017

Challenge 64 - Sum up the Range where a particular alphabet appears

By |Saturday, July 15th, 2017|Categories: Challenges|Tags: , , , |1 Comment

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 particular alphabet appears

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Mon 10 Jul 2017

Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

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),"*",", "))

 

Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

By |Sunday, July 09th, 2017|Categories: Articles|Tags: , , , , , , , , |0 Comments

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 is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.

(more…)

Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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.

(more…)