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 →
{ 0 Comments }
To split multiple lines of data appearing in one cell (originally entered via Alt+Enter) into multiple columns, try this 1. Select the data range and go to Data > Text to columns > Delimited > Next 2. Ensure that all delimiters are unchecked 3. Check the box for Other and in the type Ctrl+J > […]
Read More →
{ 0 Comments }
Assume a three column database of Article Number, Size and Quantity. The size column may have five sizes – Small, Medium, Large, XL and XXL. Not all sizes will be available for every article number. The task is to create a three column database in another worksheet, which shows only those article numbers having all […]
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 →
{ 326 Comments }
Assume a four column database with department as the second column. Entries in the department column may be repeated. A person may wish to create one worksheet tab for each department. Therefore, as and when a person names a worksheet tab as one of the departments, all rows of that department should appear on this new sheet. You […]
Read More →
{ 14 Comments }
The VLOOKUP() function accepts a definite lookup array (second input in the VLOOKUP()) specified by the end-user. In a situation where the lookup array is unknown, a function may have to cycle through various lookup ranges to cull out the required value. You may refer to my solution in this workbook.
Read More →
{ 4 Comments }
Given a large number of entries in the source of data validation, it may become cumbersome to scroll down the list to select a desired value. It would be ideal to have the user type the first alphabet and be taken to all words which start with that alphabet. The drop down list in cell […]
Read More →
{ 0 Comments }
While it is easy enough to create a floating bar chart in Excel 2003 and prior versions, the problem is with data labels which do not appear at extreme ends for showing min and max values. You may refer to my solution in this workbook.
Read More →
{ 14 Comments }
Assume that in range A2:A11, cells have been validated to allow a person to enter Y or N. In B2:B11, cells should be validated based on the entry selected by the end-user in range A2:A11. So, if a user selected Y in cell A2, then a drop down should appear in cell B2, allowing him to […]
Read More →
Determine weekday after factoring in user specified holidays
{ 0 Comments }