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

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

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 from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

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 […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Count cells between a given value

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 […]

Filter unique values and sort based on adjacent date

Question: How do I filter unique rows and sort by date? Answer: Column A and B are the original list. […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

### 2 Responses to “Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.