Key Advanced Skill: Creating List from scattered entries by Avoiding/Ignoring/Skipping empty rows

Objective

You are working on a data set and want to get your results based upon certain criteria.  Now the problem is your result contains blank rows / rows which do not qualify with your criteria. You want result as a list i.e. without blank rows. 

This model/method resolve many data picking/filtration issues,therefore in my opinion its a KEY concept.

Prerequisites

Knowledge of IF, ROW, SMALL & INDIRECT functions.

Solution Concept

Identify Rows which qualify your criteria. With SMALL function create a list of ROW numbers in ascending order. With INDIRECT pick values.

Let’s do it (See Workbook)

1.1.      Identifying Rows where Criteria is met

1.1.1. =IF(logical Test)=TRUE,"Select","") . This will display ‘Select’ in rows where criteria is met and eligible to be selected

1.1.2. =IF(E3="Select",ROW(),"") . Row numbers will be displayed for ‘Select’ Rows

1.2.      Listing Row Numbers together (This is the key concept)

1.2.1. SMALL function will get you n^th smallest number in list. Like MIN function gets you smallest value, SMALL function with ‘1’ will get you the same.

But with MIN you cannot get second smallest or third smallest. However, SMALL function allows you to find 2^nd, 3^rd, 4^th or nth smallest number.

Create list of row numbers with help of SMALL function.

1.3.      Getting Value through INDIRECT

1.3.1. INDIRECT needs a valid cell address to work (See Post on INDIRECT for complete understanding).

By adding column reference “A” or “B” with Row numbers create a valid cell address and pick values with help of INDIRECT.

Sample Workbook          

Prerequisites:

See also: 

6 Ways to Create Unique List