In range A7:A15, assume the following values A, S , empty cell, empty cell, R, A, W, A, SD. Starting from cell B17, transpose data to the right (ignoring the blank cells). Therefore, in range B17:F17, the result should be A, S, R, W SD. Solution for MS Excel 2010 and higher versions If you […]
Read More →
Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range. Furthermore, one may want to provide an additional date criteria as well. You may refer to my solution in this workbook.
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 →
{ 2 Comments }
Assume there are three worksheets, one each for 2008, 2009 and 2010. On each sheet there are two columns for Account code and Account description. Thereafter there are 12 columns (one for each month). Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]
Read More →
{ 6 Comments }
Create a data validation list from a range which has data appearing in multiple rows. I have shown two cases – one using macros and other using formulas. The question and solution have been explained in this workbook.
Read More →
{ 0 Comments }
Depending upon customer requirements specified for width, thickness and length os material, extract a report showing all records from the master stock-list which meets the requirements. The question and solution have been elaborately explained in this workbook.
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 →
{ 0 Comments }
Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days). When determining the ending date, the following two cases should be considered Case 1 Holidays are: 1. Sundays only; and 2. User specified public holidays Case 2 Holidays user specified public holidays […]
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 →
Dynamically transpose data after ignoring blank cells
{ 2 Comments }