Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
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
- Combine data from multiple sheets in excel
- Split data across multiple sheets in excel (vba)
- Return multiple values if in range in excel
- Return multiple values if above frequency criterion in excel
- Count unique and duplicate numbers from a closed workbook in excel (formula)
- Create a unique distinct text list from a range containing both numerical and text values in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Extract duplicate text values from a range containing both numerical and text values in excel





August 27th, 2010 at 10:44 am
Hi, really nice guide, but hard to understand for those who are not already Excel gurus, meaning I can't work out how to adapt the formula for what I need to do.
I've never seen the "SMALL" function before and I don't understand it's role in these formulas. "Returns the k-th smallest row number in this data set." does not mean anything to me (what does k-th mean?). I looked this up elsewhere and they said "The Small function returns the nth smallest value from a set of values." which does actually make sense to me, but what does it have to do with removing duplicates or finding unique values?
It would be useful to elaborate on this a bit and maybe explain how these formulas work when broken up into segments.