## Concatenate unique distinct values

*Article updated on February 19, 2018*

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to extract unique distinct values from a single column and concatenate the result into one cell.

The following picture shows you 4 values in column B. Value "AA" has a duplicate in cell B5. Unique distinct values are all values except duplicates.

Array formula in cell D3:

**Tip!** You can use the UDF I made if you don't have Excel 2016 and the TEXTJOIN function.

Recommended article

Quickly concatenate values into one cell [No VBA]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

#### How to enter an array formula

- Double click on cell D3
- Copy/Paste above formula to cell D3
- Press and hold CTRL + SHIFT
- Press Enter once
- Release all keys

Your formula has now a beginning and ending curly bracket, like this:

Don't enter these characters yourself, they appear automatically if you followed above steps.

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

#### Explaining array formula in cell D3

**Step 1 - Match values with themselves**

MATCH(B3:B6, B3:B6, 0)

becomes

MATCH({"AA"; "BB"; "AA"; "CC"}, {"AA"; "BB"; "AA"; "CC"},0)

and returns {1;2;1;4}

The MATCH function returns the relative position of a value in an array. It only returns the position of the first found value.

Value "AA" is found on the first position, so the first value in the array is 1. {**1**;2;1;4}

Value "BB" is found on the second position, so the second value in the array is 2. {1;**2**;1;4}

Value "AA" is found on the first position, so the third value in the array is 1. {1;2;**1**;4}

Value "CC" is found on the fourth position, so the fourth value in the array is 4. {1;2;1;**4**}

Recommended article

Identify the position of a value in an array.

**Step 2 - Compare array with relative positions**

MATCH(B3:B6, B3:B6, 0)=MATCH(ROW(B3:B6), ROW(B3:B6))

becomes

{1;2;1;4}={1;2;3;4}

and returns {TRUE;TRUE;FALSE;TRUE}

This array tells us if a value is a duplicate or not. FALSE indicates a duplicate and we don't want that in the final result.

**Step 3 - Filter unique distinct values**

IF(MATCH(B3:B6, B3:B6, 0)=MATCH(ROW(B3:B6), ROW(B3:B6)), B3:B6, "")

becomes

IF({TRUE;TRUE;FALSE;TRUE}, B3:B6, "")

becomes

IF({TRUE;TRUE;FALSE;TRUE}, {"AA";"BB";"AA";"CC"}, "")

and returns {"AA";"BB";"";"CC"}

Recommended article

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**Step 4 - Concatenate values**

TEXTJOIN(", ", TRUE, IF(MATCH(B3:B6, B3:B6, 0)=MATCH(ROW(B3:B6), ROW(B3:B6)), B3:B6, ""))

becomes

TEXTJOIN(", ", TRUE, {"AA";"BB";"";"CC"})

and returns AA,BB,CC.

The first argument in the TEXTJOIN function lets you specify the delimiting character. The second argument if you want to ignore empty strings. The third argument is the values you want to concatenate.

Recommended article

How to use the TEXTJOIN function

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

#### Functions in this article

Make sure you check out these links if you want to know more about the excel functions in the array formula above: TEXTJOIN, MATCH, IF, ROW

#### Download excel *.xlsx file

Use a drop down list to extract and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 6 Responses to “Concatenate unique distinct values”

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

https://www.youtube.com/watch?v=QJ2O07EB80Q&feature=youtu.be

Great video!

This is great. Is there a way to combine it with vlookup, please?

Gustavo,

Concatenate cell values based on a condition [No VBA]

Hello, nice, but can you try your formula with with 10000 records? It is working with 1000 but failing with 10000 as it is not removing the empty cells any more...

Tamas

Weird, you probably need a UDF.