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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct numbers across multiple sheets (3D range) in excel
  2. Combine data from multiple sheets in excel
  3. Split data across multiple sheets in excel (vba)
  4. Return multiple values if in range in excel
  5. Return multiple values if above frequency criterion in excel
  6. Count unique and duplicate numbers from a closed workbook in excel (formula)
  7. Create a unique distinct text list from a range containing both numerical and text values in excel
  8. Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
  9. Lookup values in a range using two or more criteria and return multiple matches in excel
  10. Extract duplicate text values from a range containing both numerical and text values in excel