## Extract unique distinct values from a related table

*Article updated on February 16, 2018*

In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a related table.

In these two examples there are two tables and they have a column in common (Company).

### Unique distinct values

**Array formula in cell B21:**

How to create an array formula

### Duplicate values

**Array formula in cell B21:**

**How to create an array formula**

- Copy above array formula
- Select cell B21
- Click in formula bar
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

The formula in the formula bar is now surrounded by curly brackets: {=array_formula}

**How to copy an array formula**

- Select cell B21
- Copy cell (Ctrl + c)
- Select cell range B22:B23
- Paste (Ctrl + v)

### Download excel *.xlsx file

Unique distinct values related tables2.xlsx

### 7 Responses to “Extract unique distinct values from a related table”

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

in excel sheet

i need to search three different word in eight sheet in particular cell and paste if present other wise blank

Example EE2203A,EE2204B,ME2201C

if present of any one paste EE2204B, other wise blank

karthikeyan,

can you explain in greater detail?

Hi Oscar,

You helped me out in the past, and I'm trying to understand the =Index(Match functions in an Array formula.

I have a range of data in 2 different columns on Sheet 2.

The ranges are almost identical, so much so that a simple "If" statement can show me differences.

. If they are equal display 1 else dispay 0. I don't want to filter as there is other data records in the sheet that are used in a vlookup formula.

So what would be the easiest way to have my first sheet "Sheet 1(A2)" find the "0" cells on Sheet 2 and display the values which are offset by 1 row?

In other words, for each two records on Sheet 2 that don't match, create a list of the 2nd record on "Sheet 1" with no blanks in between them.

Thanks in advance!

cwrbelis

cwrbelis,

Check the attached sheet:

cwrbelis.xlsx

Hi Oscar,

This is a question specific to your other article "How to extract a unique distinct list from a column in excel," but there wasn't a section to leave a reply, so I will ask it here.

I'm using your vba code for a user-defined function to extract unique distinct sorted values, but I want to use it in a table. As you probably know, multi-cell array formulas are not allowed in tables, so is there a way around this?

Thanks!

Hi Oscar,

I used your formula above, i have a question, it is possible to have a two search criteria?

Julius,

Yes it is.

Array formula in cell B21:

=IFERROR(INDEX(Table2[Sales persons], SMALL(IF((ISERROR(MATCH(Table2[Company], IF(COUNTIF($B$19:$C$19, Table1[Item]), Table1[Company], ""), 0)))+COUNTIF($B$20:B20,Table2[Sales persons]), "", MATCH(ROW(Table2[Company]), ROW(Table2[Company]))), 1)), "")