Tag Archives: TEXTJOIN

#Excel: Generating a Random Sampling From a List Using VBA and the TEXTJOIN Function by David Hager

You might have a need to generate a random sampling of items from an Excel list. The technique presented here accomplishes this without any helper columns. The PickRandomFromList VBA function shown below returns a random array of items from a worksheet list, the size determined by the 2nd argument of the function.

Function PickRandomFromList(rList As Range, sArray As Integer) As Variant

Dim N As Long

Dim Arr() As Variant

Dim lArr() As Variant

Dim Temp As Variant

Dim J As Long

Application.Volatile False

Arr = rList.Value

Randomize

ReDim lArr(LBound(Arr) To sArray

For N = 1 To sArray

J = CLng(((UBound(Arr) – N) * Rnd) + N)

Temp = Arr(N, 1)

lArr(N) = Arr(J, 1)

Arr(J, 1) = Temp

Next N

PickRandomFromList = lArr

End Function

It is important to note that this UDF does not recalculate with every change in the worksheet by using the line of code Application.Volatile False. It will only recalculate if a change is made in the cell containing the formula or in the specified worksheet range. The conversion of the array to a delimited list is done through the use of the TEXTJOIN function.

=TEXTJOIN(“,”,,PickRandomFromList(A2:A22,5))

PickList1

This technique may be particularly useful for the selection of random committees from an employee list. I hope that this will give you some ideas about situations requiring random sampling.

You can download the workbook here.

PickList

Advertisements

#Excel Identifying if a String is the Anagram of Another String Using the TEXTJOIN Function by David Hager

Using Excel native formulas to identify anagrams is quite rare. I could find only one example.

https://excelxor.com/2014/08/30/advanced-formula-challenge-2-results-and-discussion/

The methodology for comparing strings as anagrams starts with these formulas, which use B3 and C3 as the locations of the strings.

CodeString1=CODE(MID(UPPER(Sheet1!$B$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$B$3))),1))

CodeString2=CODE(MID(UPPER(Sheet1!$C$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$C$3))),1))

Each of these formulas returns an array of numbers corresponding to the character code of the letters in the string (converted to all UPPER). Why make an array of numbers? So that they can be sorted. So, the next formulas:

CodeStr1Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(B3))))

CodeStr2Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(C3))))

create an array of numbers that are sorted from smallest to largest. So, if both strings contain the same number and frequency of letters, the arrays will be identical. The TEXTJOIN function is used here to create strings from those arrays for comparison.

NumberStr1=TEXTJOIN(“”,,CodeStr1Sorted)

NumberStr2=TEXTJOIN(“”,,CodeStr2Sorted)

The formula indicate that the string in C3 is an anagram of the string in B3 is shown below.

=EXACT(NumberStr1,NumberStr2)

Note that this solution unfortunately does not include an Excel native formula or grouping of formulas to determine whether a string is an actual word (not possible). So, although it is not required, a VBA worksheet function is included here as part of the solution. You can look at the code here. WordPress.com will not allow me to publish an .xlsm file. So, please add a module in the VBE (Alt-F11) and copy/paste this function procedure into the module. When you save it, you will have to save it as an .xlsm file.

Public Function IsWord(ByRef Text As String) As Boolean

Dim wd As Object

Application.Volatile True

On Error Resume Next

Set wd = GetObject(, “Word.Application”)

If Err.Number <> 0 Then

Set wd = CreateObject(“Word.Application”)

If Err.Number <> 0 Then

Set wd = GetObject(, “Word.Application”)

End If

End If

IsWord = wd.CheckSpelling(Text)

Set wd = Nothing

End Function

 

Here is the resulting formula to determine whether both strings are words and anagrams.

=AND(IsWord(B3),IsWord(C3),EXACT(NumberStr1,NumberStr2))

Remember, you need the correct version of Excel 2016 (Office 365) in order to use the TEXTJOIN function.

You can download the file here.

Textjoin_Anagram

TEXTJOIN: Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager

A recent post on Twitter pointed to an article on the very good trumpexcel.com site.

https://trumpexcel.com/multiple-lookup-values-single-cell-excel/

In the article, the following statement was made.

“Can we get multiple lookup values for an item in a single cell (separated by comma or space)?

I have been asked this question multiple times by many of my colleagues. And after a lot of head-banging, I realized that it is extremely difficult (if not impossible) to do this using excel functions.”

The problem was solved there with a VBA solution. Well, let’s see if the same scenario can be solved by using only Excel formulas. To be honest, the complete solution I will demonstrate could not be easily accomplished before the introduction of the TEXTJOIN function, which appeared after the article was published.

BTW, the first mention of TEXTJOIN with unique elements can be seen in this video.

https://youtu.be/QJ2O07EB80Q

If the items in the lookup column are unique, the solution is relatively easy. The formula

=TEXTJOIN(“,”,,IF(arange=E3,brange,””)) where arange is in Column A & brange in Column B

can be array-entered in F3 and filled down (see the following figure).

mlv1

So, for regions 1-10, the corresponding lookup matches are shown in the delimited strings.

However, for the case where the lookup column contains duplicates, the task to return unique delimited strings for each region is much more difficult. The simplified form of the array to be used in building the final formula is:

cArray=INDEX(brange,N(IF(1,some_array)))

where:

“some_array”=SMALL(IF(arange=$E3,ROW(brange)-1,””), ROW(INDIRECT(“1:”&COUNT(IF(arange=$E3,ROW(brange),””)))))

There are several important parts to the SMALL array (which contains the array positions of the desired items from brange). The main array used as the first argument of the SMALL function is:

IF(arange=$E3,ROW(brange)-1,””)

In my experience, this kind of formula construction is rare, but it works.

The second argument of the SMALL function is:

ROW(INDIRECT(“1:”&COUNT(IF(arange=$E3,ROW(brange),””)))))

The really interesting thing about this formula is that it has to be dynamic with respect to each region since they have differing numbers of lookup values.

Two additional defined name formulas are needed for the final formula construction.

countArray=COUNTA(cArray)

=ROW(INDIRECT(“1:”&countArray))

which creates another dynamic array using the same techique as above.

The final formula is:

=TEXTJOIN(“,”,,IF(MATCH(cArray,cArray,0)=RowArray,cArray,””))

mlv2

You can see in the figure that none of the strings in column F contain duplicate values. As a comparison, an adjacent column using the TEXTJOIN formula from the first example shows the difference in regions 4 and 9.

mlv3

Hope that you find this technique useful. You can download the file from the following link.

MultipleLookupValues

Using TEXTJOIN: Generating a Sorted Unique Array in #Excel using Only Formulas by David Hager

I like to review articles from various Excel sites to get ideas for new creations and to use the “formula

technology” displayed there. I was reading an article from Oscar’s great Excel site at

http://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formula/

and I decided to try and extend his work by creating a sorted unique array derived from a list. I wanted this to use with one of my favorite Excel functions (TEXTJOIN). The final result is shown in the following figure.

 uSorted1

Now, to how this was made. The following formula was created by Oscar (and slightly modified by me). Is uses COUNTIF to determine the number of array items that are “greater” than the rest of the items (i.e. A>B).

UniqueArr1=IF(MATCH(COUNTIF(List,”>”&List)+1,COUNTIF(List,”>”&List)+1,0)=ROW(INDIRECT(“1:”&COUNTA(List))),COUNTIF(List,”>”&List)+1,””)

{1;21;26;16;12;””;14;19;7;””;17;25;””;10;3;29;13;””;9;8;6;18;23;24;””;””;””;””;22;””}

Now, the challenge was to create an array of positions from UniqueArr1 corresponding to the largest to smallest numbers in UniqueArr1. If you locate the largest number in UniqueArr1(29), it is in position 16 in the array. Likewise, the 2nd largest number in UniqueArr1(26), is in position 3. This was accomplished by using the following formula (although I admit that it took a while to figure this out).

=MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0)

It is important to note that the ROW array is dimensioned with COUNT(UniqueArr1), since the elements of UniqueArr1 greater than COUNT are null values. As you can see, the resulting array is correctly dimensioned.

{16;3;12;24;23;29;2;8;22;11;4;7;17;5;14;19;20;9;21;15;1}

Now, we have an array with the sorted positions in the correct order and that array can be used return itemd from the List array. A few years ago, it was thought that the INDEX function could not return an array of items, but then a great solution to this appeared at the excelxor.com site. See:

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

The resulting formula is shown below.

uSortedArr=INDEX(List,N(IF(1,MATCH(LARGE(UniqueArr1,ROW(INDIRECT(“1:”&COUNT(UniqueArr1)))),UniqueArr1,0))))

The resultant array is:

{“Bovey”;”Bullen”;”Cronquist”;”Dalgleish”;”Devenshire”;”Duggirala”;”Green”;”Hager”;”Hodge”;”Jelen”;”Kusleika”;”Manville”;”McRitchie”;”Mehta”;”Ogilvy”;”Pearson”;”Peltier”;”Pieterse”;”Puls”;”Rech”;”Umlas”}

This array can now be converted back to a string by using the TEXTJOIN function. The use of the delimiter CHAR(10) allows the result to be displayed as shown in the figure earlier if the cell is formatted with word wrap as true and is merged with lower cells. The formula (in cell K1 in the example) is:

=TEXTJOIN(CHAR(10),,uSortedArr)

You can download the file from this link. Remember, you must have a correct version of Excel 2016 for the TEXTJOIN function to work.

UniqueSorted

 

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

The following defined names are needed to construct the formula.

Rge=Sheet1!$A$2:$A$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items.

tj_subtotal

In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t

You can download the file here.

textjoin_subtotal1

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

The listing of recipes for process blending is difficult, since each recipe can contain a different number of components. Therefore, the usual listing of this recipe information is as shown in columns A and B. From the sample table, defined names are given where arange is the list of blended compounds and brange is the list of components. In this case:

arange=Sheet1!$A$2:$A$25

brange=Sheet1!$A$2:$A$25

tj_pic1

The following formula was used to make the unique list of compounds in column E.

=INDEX(arange,MATCH(0,INDEX(COUNTIF($E$2:E2,arange),0,0),0)) in E3.

Then the formula is copied down until #N/A appears in a cell. Cells containing #N/A are then deleted.

I found this formula at Oscar’s great Excel site. See:

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

Now that the unique list is in place, a comma delimited list of components can be created in column F.

Place the following formula in cell F3 and fill down.

=TEXTJOIN(“,”,,IF(arange=E3,brange,””))

This array formula compares the value in E3 to each value in arrange and if there is a match, the corresponding value in brange is returned to the array. The TEXTJOIN function converts the array to a string using comma as the delimiter.

You can download the file here.

 textjoin1

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

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

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