## Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

*Article 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:

Formula in E6:

### 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:

**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:

**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:

**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:**

copied down as far as needed.

**Array formula in E18:**

copied down as far as needed.

**Array formula in G18:**

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

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Count how many numbers between a specific value occurring multiple times

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]### 2 Responses to “Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.

I posted a similar comment, am I missing something or are you actually manually typing in those bolded values in step 2?

"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"

What do you mean when you say I am using the calculated.... are you manually looking at those values and typing them into a formula? This seems almost an impossibility given that this whole process needs to be automatic, if it is the actual case that every time you want to update this you have to read the min and max and manually change every formula on the page by hand it is just about useless.