Assume the following numbers in range C5:C22. Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]
Read More →
{ 206 Comments }
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 →
{ 3 Comments }
Assume a two column database of patient ID’s and service availed. One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed. For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]
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 →
Shade alternate band of rows in a filtered range
{ 4 Comments }