Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell). Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays. You may refer to my solution in this […]
Read More →
{ 2 Comments }
Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey. To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]
Read More →
{ 252 Comments }
Assume an MS Excel file has 4 worksheets – Sheet1, Sheet2, Sheet3 and Sheet4. Insert a sheet before Sheet1 and name that tab as Summary. On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards. Furthermore, the sheet names so generated, should be dynamic for the following changes: 1. Sheets […]
Read More →
{ 4 Comments }
In range G13:G17, assume the following entries – VG:25, NS:50, NS:20, #DIV/0! and VG:150. To sum up the values where VG appears, you may refer to my solution in this workbook.
Read More →
{ 2 Comments }
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 →
{ 72 Comments }
Given an alphanumeric string, one may want to perform the following Extract phone number from the string Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself. These numbers may be mobile numbers and/or mobile numbers. Furthermore, PIN codes may also be mentioned in the address string. One may want […]
Read More →
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 →
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 →
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 →
Calculate turn around time excluding Sundays and public holidays
{ 206 Comments }