Performing a text to rows operation

{ 20 Comments }

Given multiple text values in a cell which are separated by a semi-colon, one may want to split data into multiple rows along with existing data in other columns.  This is something similar to Data > Text to columns – the only difference being that data has to be split by rows instead of columns. […]

Read More →

List down most frequently appearing names in descending order of frequency

{ 0 Comments }

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 →

Average a range of numbers with blanks appearing at random intervals

{ 4 Comments }

With blanks appearing in a range of numbers, one may want to: 1. Average the first n numbers; and/or 2. Sum the last n numbers (in a horizontal and vertical range) Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached. You may refer to my solution in this […]

Read More →

SUMPRODUCT function to work on a range with interspersed text values

{ 0 Comments }

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 →

Secondary validation cell entry to update when primary validation cell changes

{ 30 Comments }

Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list).  Instead the secondary validation cell remains the same as what was already selected earlier. In […]

Read More →

Create a log sheet of all array formulas in a workbook

{ 0 Comments }

There may be many array formulas in a workbook.  One may want to create a two column table in a sheet named “Array formula log” which lists down sheet names and cell address of all array formulas.  Furthermore, the cell references listed on the “Array formula log” sheet should be hyperlinked. You may refer to […]

Read More →

Blanks appearing in source data not to appear in Data validation list

{ 0 Comments }

Assume a column of data with blanks appearing in a few random cells.  One may want to use this range of cells for creating a Validation drop down in a specified cell.  After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]

Read More →