A user inputs raw material purchase data in a master sheet. This data is entered by date and vendor. One may want to view individual vendor sheets at a monthly level. In the attached file, i have presented two solutions – a macro based one and a formula based one. The data layout and instructions […]
Read More →
{ 4 Comments }
In a multi column database, assume a filter has been applied on some columns. Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns. The objective is to sum numbers in a numeric column. The usual SUM() function would not work because the range of sum function should be error free. Furthmore, […]
Read More →
In a scenario where text values are interspersed with numeric data columns, the usual SUMPRODUCT function will not work. One will have to use a combination of array formulas and the SUMPRODUCT function. You may refer to my solution in this workbook.
Read More →
{ 0 Comments }
There may be many array formulas in a workbook. One may want to create a two column table in a sheet named “Array formula log” which lists down sheet names and cell address of all array formulas. Furthermore, the cell references listed on the “Array formula log” sheet should be hyperlinked. You may refer to […]
Read More →
Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array. To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.
Read More →
{ 0 Comments }
The objective is to determine the quantity of stock (of a particular type) to be transferred from “Stock surplus” locations to “Stock deficient” ones. If all the requirements cannot be met from one location, tap other locations. The final output should show the location from where stock is being transferred. Furthermore the order of determining […]
Read More →
{ 46 Comments }
A financial concept, Day Sales Outstanding (DSO), is a measure of the average number of days that a company takes to collect revenue after a sale has been made. For further clarification, read here. Herein, i have presented the solution for two different data layouts. Further, Layout 2 has two further divisions – one for computing […]
Read More →
{ 8 Comments }
Assume there are two columns with data repeated in all both columns. One may wan to extract the unique text values from a specific column based on conditions specified by end-user. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions […]
Read More →
{ 4 Comments }
Given a single column with duplicates appearing, one may want to dynamically extract unique values to another range. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions If you are using the Power Query add-in, then a […]
Read More →
Programmatically transfer data from master sheet to sub sheets with conditions
{ 45 Comments }