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

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.

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

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