## Extract unique distinct values from a related table

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

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

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

Sort a range based on value frequency

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Working with three related tables

I have written a few posts about two related tables and today I am going to show you how to […]

This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to […]

Lookups in a related table (array formula)

The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect […]

Use Conditional Formatting to do lookups in related tables

It can be really hard trying to follow a lookup in related tables. I will show you how to use […]

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

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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

Hi Oscar,

I have two different tables, some row in both tables have same data, i want to extract unique rows data by comparing both tables... How can I do this...?

Hello Kamran Mumtaz

Great question, see this article:

https://www.get-digital-help.com/2018/06/11/extract-unique-distinct-records-from-two-data-sets/

If I have a Matrix 2x10 in the following way

1 2 3 4 5 6 7 8 9 10

11 12 13 14 5 16 17 18 19 20

and want two arrange in the following way:

1 2

3 4

5 6

7 8

9 10

11 12

13 14

15 16

17 18

19 20

I have a large data file 8000x10 and the numbers are pair the first two number in first row will become first 2 numbers in first row , third and four in row 1 will be first and second in row 2, etc.