Assume there is a list of names appearing in range A2:A9. Before these names, there are abbreviations such as Dr., Mr. Mrs. etc. To worsen the situation, there may/may not be a space after the abbreviation. A sample dataset may look like this:
Dr (Mrs.) Indu Sharma
Dr. (Mr) Rakesh Singh
Mr. Mrinal Joshi
The task is to extract only the names in another column.
You may refer to my solution in this workbook.
On the Data worksheet, data for payments received from different customers is shown invoice wise. Since payments are received in tranches, they are shown in different columns - column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second tranche respectively.
One may want to generate the list of Invoice Number, Customer Name and the Amount for a particular date. Since dates are split across two columns, it will not be possible to filter the dataset. In order to filter the dataset, one will have to append data from alternate columns.
I have been able to accomplish this by using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions.
You may refer to my solution in this workbook.
Assume quantity sold date by date and City in a three column database. The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table.
The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running Total in, resets the quantity sold to 0 when the year changes.
This issue can be overcome by writing DAX formulas in a Power Pivot. You may refer to my solution in this workbook.
With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View). Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)
1. Power Query; and
2. Power Map
I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:
1. An overview of the BRIC Economies
2. Sales data analysis
Just in case you do not have Microsoft Office Professional Plus 2013, you can see the features of Power BI in this 10 minutes video of the first workbook - "An overview of the BRIC Economies".
Assume a table which lists attendees for a Company's Annual day function. In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee.
The question is to determine the count of the following family configuration:
1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)
You may refer to my solution in this workbook. I have solved this problem using Set Theory and Venn Diagram.
Assume a file with two worksheets. The first worksheet has data in column A:H (8 columns) and the second one has data from B:J (9 columns). One may want to create a third worksheet with specific columns of data appended from both worksheets - the columns to be shown in the third worksheet are not at the same position on both worksheets i.e. while the Inventory Date data is in column A on sheet1, it is in column H on sheet2.
You may refer to my solution in "Resultant Data - final" worksheet of this workbook.
I have solved this problem using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions. The result is dynamic and refreshable (just as in a Pivot Table).
Any lookup function i.e., VLOOKUP(), LOOKUP(), INDEX()/MATCH(), HLOOKUP() requires the text lookup variable (the first input of the VLOOKUP(), HLOOKUP() etc. ) to be spelled exactly the same as the entries in the lookup column. If the spelling does not match, the result is #NA. To some extent, inexact searches can be performed by concatenating wildcard characters (? and *) to the lookup variable.
The task is to increase MS Excel's tolerance towards spelling mistakes and inexact text matches across two tables. For e.g., when performing a lookup, somehow MS Excel should be able to understand the Delhi City is the same as City, Delhi. In order to perform, inexact searches, you can download and install Microsoft's Fuzzy lookup add-in from here.
I have run this add-in on two live datasets in this workbook.
Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads. While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two.
If one attempts to write a calculated item formula within a Pivot Table, then variance rows will appear even for cases where Budget figures are equal to Actual figures i.e. where variance is equal to 0.
You may view the question, actual dataset, the failed Pivot Table attempt and final Power Query attempt in this workbook.
One worksheet shows the date wise courses (External Course, Internal Course and Elearning Course) taken by staff members. The data also shows the Line Managers that they report to. Another worksheet lists down all staff members and the Line Managers that they report to.
From data on the first worksheet, one can easily determine (via a Pivot Table) the Elearning courses taken by various staff members (along with their Line Managers). However, a Pivot Table will not show the staff members (along with their Line Managers) who did not take the Elearning Course.
You may refer to the data set and my workaround in this workbook. My solution uses the Microsoft Power Query add-in for MS Excel 2010 and higher versions. One can download and install the tool from here.
Performing calculations or writing formulas on large databases pose the following major problems:
1. Time taken to process is unduly long; and
2. File size increases disproportionately
A typical problem is fetching data from another worksheet or workbook via the VLOOKUP() function. Quite often, when one confirms the VLOOKUP() formula in a single cell with the Enter key, then at the bottom right of your MS Excel screen (the task bar), a white progress bar appears which reads "Calculating (4 processors) 1%". Once has to wait for the processing to complete before copying the formula down. The process of copying is obviously just as time consuming and resource intensive.
In these situations, a workaround is the JOIN the tables in MS Access and then get the data back in MS Excel. For accomplishing this, one must have at least some working knowledge of "SQL Query" writing.
If you are using the Power Query add-in for MS Excel 2010 and higher versions, then one can fetch data from another worksheet or workbook without writing a single formula. Furthermore, there is absolutely no lag in generating the result.
You may refer to my Power Query solution in this workbook (This is a 20 MB file)