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:

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

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 many many cell values in excel is not easy. The Concatenate function allows you to only reference a single […]

Comments(86) Filed in category: Concatenate, Excel, Textjoin

How to enter an array formula

  1. Double click on cell D3
  2. Copy/Paste above formula to cell D3
  3. Press and hold CTRL + SHIFT
  4. Press Enter once
  5. 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.

Comments(2) Filed in category: Count values, Excel

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

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

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

IF function explained

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

Comments(9) Filed in category: Excel

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

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

Comments(4) Filed in category: Excel, Textjoin

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

Concatenate unique distinct values.xlsx