A range of cells has multiple background colours. Depending upon the colour selected by an end user via a cell drop down, those cells (along with information in other columns) should get transferred to another worksheet. You may refer to my solution in this workbook.
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 →
{ 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 →
{ 12 Comments }
In column A, there are some alphanumeric entries. Somewhere in the alphanumeric entries (beginning, middle or end) there are the following: 1. Dates 2. Numbers (without decimals) 3. Numbers (with decimals) Furthermore, there may or may not be spaces between numbers/dates and text entries. In Excel 2010 and prior versions, the only way to solve this problem would […]
Read More →
{ 0 Comments }
Assume some names are entered in column A. The names can be only First name, First name and Middle name or First name, Middle name and Surname. You may refer to my formula in this workbook.
Read More →
{ 0 Comments }
Given a sentence which has an e-mail address positioned anywhere i.e. beginning, middle or in the end, one may want to extract only the e-mail address from the string. You may refer to my formula based solution in this workbook.
Read More →
{ 2 Comments }
In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8. In cell B8, a user enters 8. One may want to answer the following questions: 1. Return the closest number which is less than or equal to the number in cell B8 2. Return the closest number which is greater than or equal to the […]
Read More →
Extract information based on background colour
{ 0 Comments }