Using the Excel TEXTJOIN Function To Return Unique Items In A One-Cell Delimited String From A 2D and 3D Range By David Hager

7 Replies

The TEXTJOIN function was recently introduced in Excel 2016, and a number of fascinating formula solutions have been created with this function. One of these formulas used TEXTJOIN to create a one-cell delimited string of unique items from a list. To see how that was done, go to:

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

The methodology for making a 1D array from a 2D Excel range was created and described in great detail at:

https://excelxor.com/2014/11/08/unique-alphabetical-list-from-several-columns/

It was subsequently used to make a unique list from that range. The following formula uses a slightly modified version of that methodology.

 

=TEXTJOIN(“,”,,IF(MATCH(Arry4,Arry4,0)=Arry1,Arry4,””))

 

where the required defined named formulas are:

 

Range1 =Sheet1!$H$2:$L4)

Arry1=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)))

Arry2=1+INT((Arry1-1)/COLUMNS(Range1))

Arry3=1+MOD(Arry1-1,COLUMNS(Range1))

Arry4=INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))

 

Again, these formulas were not created by me, but the TEXTJOIN shown above does create a one-cell comma-delimited string from the 2D range Sheet1:!$H$2:$L4).

For a VBA solution to this same problem, see:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-unique-list-from-multi-column-table/

I needed a way to transform a 3D range in Excel to a 2D array. Luckily, this had already been done recently. Here is the formula:

Range_3D=CELL(“contents”,IF(1,+INDIRECT(Sheets&TEXT(MODE.MULT(ROW(Range1)*10^5+COLUMN(Range1),ROW(Range1)*10^5+COLUMN(Range1)),”!R0C00000″),)))

This formula was created by MichaelCH as part of the collaboration at the amazing excelxor.com site.

See: https://excelxor.com/2016/04/08/advanced-formula-challenge-13-single-array-containing-all-entries-from-a-given-range-in-multiple-worksheets

Since this formula creates a 2D array from a 3D array, I was hopeful that it could be used in the same way that a “real” Excel 2D range is transformed to a 1D array. In particular, I was concerned that the COLUMNS function would not work on a 2D array, but it did! I just had never tried it before.

In order to modify Arry1 for correct scaling for the 3D range, the following defined formulas were needed:

 

Sheets={“Sheet1″,”Sheet2″,”Sheet3”,”Sheet4”}  ‘in this example

ShCnt=COUNTA(Sheets)

 

The modification of Arry1 to 3D scaling is shown below:

 

Arry1_3D=ROW(INDIRECT(“1:”&COLUMNS(Range1)*ROWS(Range1)*ShCnt))

along with the other transform array formulas.

Arry2_3D=1+INT((Arry1_3D-1)/COLUMNS(Range_3D))

Arry3_3D=1+MOD(Arry1_3D-1,COLUMNS(Range_3D))

Arry4_3D=INDEX(Range_3D,N(IF(1,Arry2_3D)),N(IF(1,Arry3_3D)))

 

The resulting formula using TEXTJOIN affords the desired one cell delimited string on unique items from a 3D range.

 

=TEXTJOIN(“,”,,IF(MATCH(Arry4_3D,Arry4_3D,0)=Arry1_3D,Arry4_3D,””))

 

Apart from using the created array with TEXJOIN, the transforming of a 3D range in Excel to a 1D array is also noteworthy.

Here is the link to download the file. Remember though, that you have to have the Excel version in Office 365 in order for the TEXTJOIN formulas to work.

uniquetextjoin