Author: Oscar Cronquist Article last updated on December 02, 2010


In this post I will show you how to extract unique distinct numbers from a closed workbook. There can´t be any blank cells in the cell range. You can only extract numbers with this method.

Unique distinct numbers are all numbers but duplicates are merged into a single number.

Extract unique distinct numbers from closed workbook

Here is a picture of the numbers in the closed workbook.

Here is how to extract unique distinct numbers from closed workbook.

Array formula in cell A1:

=SMALL(IF(FREQUENCY('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)>0, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, ""), ROW(A1)) + CTRL + SHIFT + ENTER.

Copy cell and paste it down as far as needed.

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) Returns the k-th smallest row number in this data set.

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

Related information:
Combine data from multiple sheets in excel