Category: DATA EXTRACTION

Extract information based on background colour

{ 0 Comments }

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 →

Split multiple lines of data in one cell to multiple columns

{ 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 →

Dynamically extract unique values with multiple conditions

{ 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 →

Dynamically extract unique values with no conditions

{ 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 →

Split data into multiple tabs

{ 324 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 →

Extract data from unknown lookup range

{ 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 →

Extract numeric data and dates from string

{ 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 →

Return closest numeric match

{ 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 →