This Page: www.cpearson.com/excel/ListFunctions.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 02-Jul-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

List Functions

This page describes various formulas and VBA procedures for
working with lists of data.

Introduction

There are a variety of methods and formulas that can be used with lists of data. The examples that use two lists assume you have two named ranges, List1 and List2, each of which is one column wide and any number of rows tall. List1 and List2 must contain the same number of rows, although the need not be the same rows. For example, List1 = A1:A10 and List2 = K101:K110 is legal because the number of rows is the same even though they are different rows.

You can download a example with these formula here.

For a VBA Function that returns a list of distinct values from a range or array, see the Distinct Values Function page.

Listing Distinct Elements In A List

You can use a simple formula to extract the distinct elements in a list. Suppose your list begins in cell C11. In some cell, enter

=IF(COUNTIF($C$11:C11,C11)=1,C11,"")

and then fill this formula down for as many rows as the number of rows in your data list. This formula will list the distinct items in the list beginning in cell C11. In the image to the left, the original data is shown in red and the results of the formula are shown in blue.

In the data shown in the image, the results are in a column adjacent to the original data. This is for illustration only. The result data may be anywhere on the worksheet, or, for that matter, on another worksheet or even in a separate workbook. The only restriction is that you must fill the formula down for at least as many rows as there are in the data list.

See No Blanks for a formula to remove the blank cells in the result list to have all the distinct entries appear at the top of the result list.

Extracting Elements Common To Two Lists

This formula assumes that the list that will contain the elements common to both lists is a range named Common and that this range has the same number of rows as List1 and List2. This is an array formula that must be array entered into a range of cells (see the Array Formulas page for more information about array formulas). Select the range Common and type (or paste) the following formula into the first cell, then press CTRL SHIFT ENTER rather than just ENTER. This is necessary since the formula returns an array of values.

=IF(NOT(ISERROR(MATCH(List1,List2,0))),List1,"")

The result of this formula is an array of the values that exist in both List1 and List2. The positions of elements in the resulting list will be the same as the positions in List1. For example, if List1 has 'abc' in its 3rd row and List2 has 'abc' in the 8th row, 'abc' will appear in the 3rd row, not the 8th row, of the result list. If an element in List1 does not exist in List2, the element in Common of the unmatched item in List1 will be empty.

The image to the left illustrates several aspects discussed previously. First, we have three named ranges, List1, List2, and Common. Second, all three ranges as the same size (10 rows in this case) but are all different sets of rows. Finally, the position of the elements in the Common range match the positions of elements in List1, not List2.

Extracting Elements From One List Not On Another List

You can also use a formula to extract elements that exist in one list but not in another. Again, it is assumed that you have two named ranges, List1 and List2 of the same size. Create a new named range called In1Not2 the same size as List1. Enter the followng formula in the first cell of the new range ("In 1 Not 2") and press CTRL SHIFT ENTER rather than ENTER. This is an array formula so it must be entered with CTRL SHIFT ENTER rather than ENTER in order to work..

=IF(ISERROR(MATCH(List1,List2,0)),List1,"")

This formula will return the elements in List1 that do not appear in List2.

The order of the elements in the result list correspond to the position of that element in List1.

Conditional Formatting Lists

You can use Excel's Conditional Formatting tool to highlight cells in a second list that appear or do not appear in a master list. Excel
does not allow you to reference other sheets in a Conditional Formatting formula, so you must use defined named. Name your master list
Master and name your second list, whose elements are to be conditionally formatted,
Second. Open the Conditional Formatting dialog from the *Format* menu. In that dialog, change
*Cell Value Is* to *Formula Is*. To highlight elements in the Second list that appear in the
Master list, use the formula

=COUNTIF(Master,OFFSET(Second,ROW()-ROW(Second),0))>0

To highlight cells that appear in Second but not in Master, use Conditional Formatting as above but use the following formula:

=COUNTIF(Master,OFFSET(Second,ROW()-ROW(Second),0))=0

You can download a example with these formula here.

This page last updated: 14-July-2007