By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel (See comments). I have no clue if that is true or not but my intention now is to develop that formula a bit further.

Values across multiple sheets are sometimes refered to as a 3D range.

Calculate min and max values in 3D range

Formula in C6:

=MIN(Sheet1:Sheet3!$B$2:$D$4) + Enter

Formula in E6:

=Max(Sheet1:Sheet3!$B$2:$D$4) + Enter

Extract unique distinct values from a 3D range

Unique distinct values are all values in a range or column but duplicates are merged into one distinct value. See example picture below.

I am using the calculated min and max values (bolded) in the array formula in B9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))<>0, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Extract unique values from a 3D range

Unique values are values existing only once in a list or range. See example picture below.

I am using the calculated min and max values (bolded) also in the array formula in E9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))=1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Extract duplicate values from a 3D range

Once again I am using the calculated min and max values (bolded) in the array formula in G9:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW($1:$7))>1, ROW($1:$7), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Putting it all together

In cell B18, E18 and G18 I have "merged" the min max formulas with the unique/duplicate formulas.

Array formula in B18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))<>0, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Array formula in E18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))=1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Array formula in G18:

=SMALL(IF(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))))>1, ROW(INDIRECT(MIN(Sheet1:Sheet3!$B$2:$D$4)&":"&MAX(Sheet1:Sheet3!$B$2:$D$4))), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Keep in mind, indirect is a volatile function.

Download excel tutorial file

Return unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

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

ROW(reference) Returns the rownumber of a reference

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

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

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.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string

Related posts:

Count unique distinct numbers across multiple sheets (3D range) in excel

merge two sheets with array formula

Search three columns on three sheets, part 2

Extract duplicate text values from a range containing both numerical and text values in excel

Create a unique distinct text list from a range containing both numerical and text values in excel