Introduction

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:

Copy cell and paste it down as far as needed.

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
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