Archives
now browsing by author
Read Data into a Dictionary of Arrays
There are many scenarios where we need to categorize and split raw data.
In this scenario, we will split raw data into new workbooks, by category, using a Dictionary of Arrays.
Normally, it’s best to combine data into a single location, as I did in this article.
The main challenge is to find an efficient way to read data into categories:
- the table might not be sorted by category:
- reading and writing data row by row is very slow.
Download the demo file to try the solution:
Which is the most efficient way to collect and write data?
Based on my experience, even reading data row by row from a list object is a slow operation, the fastest way to read data is to load it into an array.
A bi-dimensional array is the best option to paste data into a worksheet, fits perfectly into a range of cells that has the same dimensions: rows and columns.
Because we want to separate data by category, we have to create an array of data for each category.
We will store the arrays into a dictionary object in this exercise, using category as a dictionary key.
Combine data files with Power Query
Data variety is a challenge when you need to combine it into a single table. If data is consistent over time, the usual tools within power Query will not fail, but this scenario rarely happens in the real life.
Most common issues when combining data:
- there is a change in headers, refreshing the query fails to identify new headers;
- data comes in a different format than expected: xlsx, csv.
Download the demo files to try the solution:
Make sure you unzip the files on a location from your computer, then update the path from Settings sheet (in the file named GetCSV and XLSX from folder.xlsx) to point to your Data folder new location.
Why Power Query fails to refresh when there is a change in source data?
When you use the default tool “Combine Files” when you Get Data from Folder with Power Query, the application will analyze the first data file from that folder, or you can select the file that will serve as reference.
Pattern Matching in Power Query
In Power Query there is no tool yet for matching regular expressions (patterns).
Only in Power BI we can run scripts in R and Python, hopefully these languages will be added to Excel Power Query.
Until then, to solve this problem, with a little imagination, we can design our own pattern matching process.
Here is a file you can download and test:
Read MoreHow to Fill PDF Forms in Excel
Fill PDF Forms in Excel
In a previous article: Use Excel to populate PDF Form Fields, I presented a manual method to transport data between Excel and PDF forms, that can be used without a paid version of Adobe Acrobat.
If you have Adobe Acrobat Pro installed OR a trial version (even if it expires and you don’t want to purchase a license), then you can use an Excel App to Fill Out PDF Forms automatically, directly from Excel.
LEN function: Basic and Advanced Examples
LEN function type: Text
Description: Returns the number of characters in a text string
LEN is a basic function widely used, many formulas built for text processing will need this function.
Take a look at the advanced applications examples:
Applications:
- Get the text with the lowest length from a column (first match only)
- Count how many cells within a range of cells have a text length higher than normal
SUBSTITUTE function: Basic and Advanced Examples
SUBSTITUTE function type: Text
Description: Substitutes the old text with new text in a text stringBeyond its basic use, which is fairly simple, the SUBSTITUTE function can be very useful combined with other functions, to get your desired results. Take a look at the advanced applications examples:Applications:Working with Excel Functions and Formulas
In this article on Excel Functions and Formulas :
1. Excel Intellisense: Formula AutoComplete 2. Working with Insert Excel Functions Wizard 3. Debug Complex Formulas: The Magic F9 and Ctrl+Z 4. Display items using display triggersCan’t Open Pivot Table Source file
Can’t Open Pivot Table Source…
Like a good Excel fan, I switched to Excel 2016 to try the new version.While working on a Dashboard for a client, with a lot of Pivot Tables and charts in it, at some point, after moving some pivot tables and charts into other sheets, within the same workbook, I received this unexpected error message – Can’t Open Pivot Table Source:
Can’t Open Pivot Table Source file
Working With a Defined Name
What is a Defined Name?
In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.Selecting Excel Table Components