New Excel #2 – Comparing String to Determine if They Are Anagrams Using Dynamic Arrays

One of my previous posts described a methodology for determining if two strings were anagrams – that is, if they contained the same letters in the same frequency, but in a different order.

https://dhexcel1.wordpress.com/2017/04/10/excel-identifying-if-a-string-is-the-anagram-of-another-string-using-the-textjoin-function-by-david-hager/

If you read that article, you will see that several complex formulas were used in the solution to that challenge. However, the following formula made with two of the new dynamic array functions (SORT and SEQUENCE) provides a simple solution.

=TEXTJOIN(“”,,SORT(MID(str,SEQUENCE(LEN(str)),1)))

So, if the variable is used with these two strings (str1 = “hardest” and str2 = “thesard”) in this formula, both return the same result string, “adehrst”, which is the sorted string for each. Thus, the following formula would return TRUE, indicating that they are anagrams.

=TEXTJOIN(“”,,SORT(MID(str1,SEQUENCE(LEN(str1)),1)))  =

TEXTJOIN(“”,,SORT(MID(str2,SEQUENCE(LEN(str2)),1)))

Stay tuned for more posts using the dynamic arrays!

#Excel Data Validation – Non-Contiguous Ranges and Changing Data Validation List after Picking

I recently saw this challenge for creating a data validation list from 2 non-contiguous ranges.

https://www.sumproduct.com/blog/article/monday-morning-mulling-december-challenge

Then, while looking up current information about data validation tricks, I reread this post on Debra Dalgleish’s Excel site, which showes a way to change the data validation list based on items picked.

http://www.contextures.com/xlDataVal03.html

I decided that I would try to combine both of these techniques, while at the same time creating the required data validation list without the need for helper columns. When I started on this, I was not sure that it would be possible, but that is the kind of challenge I like 😊.

I had previously published a method for combining non-contiguous ranges into a comma-delimited string.

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

Using this technique along with modifying the ranges to exclude blank values, the following formula produces a delimited string combining the elements of two ranges named List1 and List2, as shown in the figure.

TJ_TLists =TEXTJOIN(“,”,TRUE,IF(ISBLANK(List1),””,List1),IF(ISBLANK(List2),””,List2))

It is important to note here that any number of ranges (rectangular, non-contiguous or 3D) can be combined in this step to afford the data validation list in the final step. As an example, see:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The next formula converts this delimited string into an array.

CombinedDV =TRIM(MID(SUBSTITUTE(TJ_TLists,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(TJ_TLists)-LEN(SUBSTITUTE(TJ_TLists,”,”,””))+1))*999-998,999))

Unfortunately, an array cannot be used directly as a data validation list. But, since there is more work to do to create data validation that can be used as a pick list, the following formulas are needed.

MatchArr=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),CombinedDV,””)

affords {“”;”b”;”c”;”d”;”e”;”f”;”g”;”h”;”I”;”j”;”k”;”l”;”m”;”n”;””}

MatchRow=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),””)

affords {“”;2;3;4;5;6;7;8;9;10;11;12;13;14;””}

Then in cell H2 is entered the formula =INDEX(MatchArr,SMALL(MatchRow,ROW()-1)),which is filled down until a formula returns an error. This is the range to be used as a data validation list.

As shown in the figure, the range where data validation is applied

SelectDV=DVSheet!$B$2:$B$16

contains an “a” and an “o”.

Then, the formula used for the data validation list is

DVList=DVSheet!$H$2:INDEX(DVSheet!$H$1:$H$16,MATCH(TRUE,ISERROR(DVSheet!$H$1:$H$16),0)-1)

So, when the data validation is used in its current state, the list will not contain those two letters.

I hope that you find this useful.

The example file can be downloaded here.

DV_TwoLists

#Excel: Extracting an Array of Words From a Sentence

 

The following formula will create an array of words from a string (sentence).

= ArrayFromSDS(TEXTJOIN(“”,,IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,IFERROR(CHAR(64+MATCH(MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“),ROW(INDIRECT(“1:”&LEN(A1))),1),CHAR(64+ROW(INDIRECT(“$1:$26″))),0)),””))))

Here is an explanation of how it works.

The array used in the 3^rd argument of the TEXTJOIN function starts with the 1^st part of the IF formula, shown below.

IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,

which keeps any space from the string in cell A1. The rest of the IF formula

IFERROR(CHAR(64+MATCH(MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“),ROW(INDIRECT(“1:”&LEN(A1))),1),CHAR(64+ROW(INDIRECT(“$1:$26″))),0)),””)

returns only letters from the string in A1. The 1^st argument of the MATCH function in this construction,

MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“), ROW(INDIRECT(“1:”&LEN(A1))),1)

is very similar to the 1^st part of the IF function, but it has one important difference.

Instead of using the string from cell A1, the formula SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“) is used instead. The reason for doing this is that the MATCH function recognizes the * and ? symbols as wildcard searches. So, if the 2^nd argument of the MATCH function does NOT contain a * or ?, the character “A” will be returned instead (if not removed from the core string).

The 2^nd argument of the MATCH function is

CHAR(64+ROW(INDIRECT(“$1:$26”)))

which returns an array of letters from A TO Z.

The result of the MATCH function is an array with numbers from 1-26 for positions in the string with letters and “” if not. For example, if the string in A1 is “ AAx,d a.”x~y*z”. c?e! ”, then the MATCH array will return

{1;1;24;#N/A;4;#N/A;1;#N/A;#N/A;24;#N/A;25;#N/A;26;#N/A;#N/A;#N/A;3;#N/A;5;#N/A}

This array is particularly useful in this specific case, since the numbers can be converted to the letters in the string by using the CHAR function (along with the IFERROR function to turn errors to an empty string). That converts the array to

{“A”;”A”;”X”;””;”D”;” “;”A”;””;””;”X”;””;”Y”;””;”Z”;””;””;” “;”C”;””;”E”;””}

Now, this array can be used as the main argument in the TEXTJOIN function to afford the string

AAXD AXYZ CE

Now, using the ArrayFromSDS user-defined function (shown below)

Function ArrayFromSDS(MyString As String)

ArrayFromSDS = Split(MyString, ” “)

End Function

produces this array of words.

{“AAXD”,”AXYZ”,”CE”}

HTH!

 

#Excel: Remove Multiple Characters From a String Using The TEXTJOIN Function and Without Using the SUBSTITUTE or REPLACE Function

 

There are a number of examples of the removal of characters from a string which utilize nested SUBSTITUTE or REPLACE functions. However, they are hard-coded in that the formulas are built with a set number of characters to remove based on the times that the SUBSTITUTE function is used. The formula methodology I am presenting here is more flexible and robust than previous solutions.

In this example, I am trying to remove all punctuation from a string, specifically the one shown below from cell A1. You will note that this string contains five different punctuation symbols, several occurring more than once.

x,da.”xyz”.c?e!

The following array formula removes those symbols

=TEXTJOIN(“”,TRUE,IF(ISERROR(MATCH(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),{“,”;”.”;”?”;”!”;””””},0)),MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),””))

and affords the desired string shown below.

xdaxyzce

HTH!

#Excel: Most Frequent Item in a List of Delimited Strings

 

rng is a defined name range on the worksheet with each cell containing delimited strings. Although it does not necessarily have to be a 1-column list, most examples of delimited strings in a range are of this type. To convert this range to an array, use the following formula.

Define arr as =ArrayFromCDS(TEXTJOIN(“,”,,rng))

where the VBA UDF is shown below.

Function ArrayFromCDS(MyString As String)

ArrayFromCDS = Split(MyString, “,”)

End Function

So, arr is a 1-D array of all of the delimited values from each cell of the range. Then, use this formula

=INDEX(arr,MODE(MATCH(arr,arr,0)))

to return the most frequent item.

Insert a Line-Feed Between Every 3rd Character Using an Excel Worksheet Formula

 

Per an Oz Du Soleil post on how to separate 5 area codes in a single 15 character string with line breaks using Power Query

https://m.youtube.com/watch?v=yorGlCrfqY0  

here is a way to do the same thing using a worksheet formula.

With the string in cell A1. Enter the following array formula in a cell.

=TEXTJOIN(CHAR(10),,MID(A1,{1,4,7,10,13},3))

Make sure that you select word wrap enabled for the cell containing the formula.

Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150

 

 

Returning a List of Formulas Based on a Specific #Excel Function by David Hager

I recently published an article which showed how to document the existing formulas in a range.

https://dhexcel1.wordpress.com/2017/08/17/documenting-formulas-in-an-excel-range-using-the-formulatext-isformula-and-textjoin-functions-by-david-hager/

You might want to go back and read that article, since the formula demonstrated there was the starting point of the formula presented here.

I wanted to modify that formula so that it would return formulas from a range that contained a specific Excel function. The desired formula for that purpose is shown below.

=TEXTJOIN(CHAR(10),TRUE,IF(NOT(ISERROR(FIND(Function&”(“,FORMULATEXT(fRange)))),ADDRESS(ROW(fRange),COLUMN(fRange))&”:”&FORMULATEXT(fRange),””))

where fRange is the defined range E2:F14

and

where Function is a defined range (in this case, K1).

One point of interest is that this formula does not require the use of ISFORMULA (as in the previous article) to validate whether a cell contains a formula since, for example, the string “SUM(“ would not be found in a cell not containing a formula.

So, this formula “looks” in each cell in fRange and if the function name in K1 is found in a cell, a string with the cell address and the formula is added to an array, which is processed and formatted by the TEXTJOIN function in cell K2 to afford the desired list. In the following figure, the result can be seen for finding formulas containing “SUM”.

In another example, the cells containing the FIND function are returned.

Note that even though some of the formulas return errors, this demonstration still shows the actual formula from each cell meeting the desired criteria.

Finally, if the value for the cell is desired, an expression for that can be added to the main formula. I do not have any plans to do that.

I hope that you find this technique useful.

You can download the example file here.

FormulaTextByFunction

#Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges

 

What if I told you that I had 2 non-contiguous 3D ranges in an Excel workbook and I wanted to return a single 1D array from those ranges. Impossible, right? No, it turns out that it is “easy”.

Prior to the introduction of the TEXTJOIN function, this would likely have been impossible. But, this function accepts native 3D ranges as range arguments. See:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

It would have been nice if the technique was only a VBA solution, but although Textjoin is a VBA worksheet function in Excel, VBA will not accept a native 3D range as an argument. Likewise, a pure Excel formula solution would have been nice, and a method dows exist to do this

https://dhexcel1.wordpress.com/2017/02/07/calculating-aggregation-for-internal-numbers-from-strings-in-a-range-by-david-hager/

but it has severe limitations which prevents its use with a relatively large number of cells (maybe 150). The total number of characters that a cell can contain is 32,767 characters. This solution assumes that an average of 6 characters per cell plus a comma for each gives an approximate number of 4500 cells allowed.

Here is the solution.

=ArrayFromCDS(TEXTJOIN(“,”,TRUE,Sheet1:Sheet3!$B2:$D$6,Sheet1:Sheet3!$G$2:$G$6))

It consists of the TEXTJOIN worksheet function with 2 non-contiguous 3D ranges arguments

TEXTJOIN(“,”,TRUE,Sheet1:Sheet3!$B2:$D$6,Sheet1:Sheet3!$G$2:$G$6)

And a simple VBA function which converts a comma delimited string into a 1D array.

Function ArrayFromCDS(MyString As String)

ArrayFromCDS = Split(MyString, “,”)

End Function

In the example file, the array produced contains all of the elements of the two 3D ranges (shown below).

{“Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name3″,”Name7″,”Name2″,”Name3″,”Name10″,”Name2″,”Name3″,”Name13″,”Name2″,”Name3″,”Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name3″,”Name7″,”Name2″,”Name3″,”Name10″,”Name2″,”Name3″,”Name13″,”Name2″,”Name3″,”Name1″,”Name2″,”Name3″,”Name4″,”Name2″,”Name7″,”Name7″,”Name2″,”Name11″,”Name10″,”Name2″,”Name15″,”Name13″,”Name2″,”Name19″,”a”,”b”,”c”,”d”,”a”,”b”,”d”,”e”,”b”,”d”,”e”,”f”,”m”,”e”,”f”}

The figure shows this formula in cell I2.

I hope that you will find this useful.

The example file can be downloaded here.

TJ_3dTo1dArray

Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager

 

Documenting Excel formulas is an important step in the auditing of spreadsheets. Presented here is a new technique to aid in that process.

The utility of the recently added FORMULATEXT, ISFORMULA and TEXTJOIN functions is sometimes underestimated. The FORMULATEXT and ISFORMULA functions were introduced with Excel 2013 and the TEXTJOIN function is available only the Excel 2016 version which comes with an Office 365 subscription. In the technique demonstrated in this article, al three of these functions are used in a single formula. For the result, see the following figure:

 

The key formula (in cell K1) is

=TEXTJOIN(CHAR(10),TRUE,IF(ISFORMULA(fRange),ADDRESS(ROW(fRange),COLUMN(fRange))&”:”&FORMULATEXT(fRange),””))

To understand how this formula works, lets break it down into its main parts.

ADDRESS(ROW(fRange),COLUMN(fRange)) returns an array of cell addresses for the desired range (in this case, fRange, which is E1:F13).

FORMULATEXT(fRange) returns an array of formulas for that range.

When these two formulas are concatenated with a colon, you get an array that looks like this.

{#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A;”$E$13:=SUM(E1:E12)”,”$F$13:=SUM(F1:F12)”}

In this example, formulas only exist in cells E13 and F13, with the rest of the array elements as #N/A. When the ISFORMULA function is applied to the range is question, the result is

{FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,TRUE}

So, the final array created by the IF function looks like this.

{“”,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;”$E$13:=SUM(E1:E12)”,”$F$13:=SUM(F1:F12)”}

When this array is processed by the TEXTJOIN function, it affords a string of formulas with their corresponding cell locations.

Note that this technique can also be used with non-contiguous ranges. Since the TEXTJOIN function can use additional arrays as arguments (see https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/ ), the same array formula used in the 3^rd argument of this TEXTJOIN example can be used in subsequent arguments, provided that the desired ranges for those arrays are different.

You can download the example file here.

FormulaText_TEXTJOIN

 

Extracting Ingredients from an #Excel List by Cost Category by David Hager

Disclaimer: This technique uses the TEXTJOIN function. You need the Excel version included in Office 365 for the TEXTJOIN formula to work.

There are quite a few examples of a material based on ingredients. These include recipes, blends, paint mixtures, etc. Presented here is a method of extractiong from a list the ingredients for each mixture based on whether they have a low, medium or high cost.

The main list has 3 columns: Mixture, Ingredient and Cost. The lookup table has a list of ingredients based on cost category, as shown in the figure.

 

The main formula from cell G2 is

=TEXTJOIN(“, “,TRUE,IF(((Mixture=$F2)+(Cost=G$1))=2,Ingredient,””))

This formula is filled to complete the lookup table.

If the mixture and the cost are correct for the formula based on its position in the table, it will return the corresponding ingredient into an array. The other array elements are left blank. Then, the TEXTJOIN function concatenates the elements of the array, excluding blanks.

I hpe that you find this useful!

You can download the file here.

IngredientListByCost

 

#Excel Short and Sweet Tip #28: Jazzing Up a Star Rating by Using the TEXTJOIN by David Hager

 

It is relatively simple to construct a star rating string (in this case 8 out of 10) by using the REPT function in the following formula.

=REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2)

gives ★★★★★★★★☆☆

However, by using the TEXTJOIN function and an array of the characters from the previous formula using the MID function, the following array formula can insert spaces between the stars.

=TEXTJOIN(” “,,MID(REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2),ROW(INDIRECT(“1:10”)),1))

gives ★ ★ ★ ★ ★ ★ ★ ★ ☆ ☆

Of course, any character (a dash) can be be used as the “spacer” in this formula construction.

=TEXTJOIN(“-“,,MID(REPT(UNICHAR(9733),8)&REPT(UNICHAR(9734),2),ROW(INDIRECT(“1:10”)),1))

gives ★-★-★-★-★-★-★-★-☆-☆

This technique is not limited to stars, so I am sure that you will find other great uses for this!

List of Priority Items from Another List Using #Excel TEXTJOIN Function by David Hager

 

I mentioned in the previous article:

https://dhexcel1.wordpress.com/2017/08/03/creating-a-set-of-custom-instructions-using-the-excel-textjoin-function-by-david-hager/

that there were variations on the theme for returning items to a string based on a specified criteria. The variation I present here is reorder items from a list based on the selected priority. In the following figure, there is a list with a column containing items and a column containing priority selection of those items.

The goal is to create a string that orders the items based on the priority selections in the adjacent column.

Although the title of this article implies that TEXTJOIN is an important part of this technique, the heart of it is to be found in the following forrmula.

=INDEX(Item,N(IF(1,MATCH(ROW(INDIRECT(“1:”&COUNT(Priority))),Priority,0))))

The ability of the INDEX function to return an array of items is explained in this article.

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

If that formula is the heart, then this part of the formula is the magic.

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

which evaluates to {5;7;10;2;3}, the positions of the priorities from 1 to 5. The rest of the master formula coerces the INDEX function to return the desired items in the desired order. That array is used in this TEXJOIN formula to obtain the desired string.

=TEXTJOIN(CHAR(10),,INDEX(Item,N(IF(1,MATCH(ROW(INDIRECT(“1:”&COUNT(Priority))),Priority,0)))))

The result is shown below.

Note that the 1^st argument of this formula is the delimiter CHAR(10). If desired, you can use a space delimiter to make a paragragh form of the string.

The file can be downloaded here.

TJ_Item_Priority

 

Creating a Set of Custom Instructions Using the #Excel TEXTJOIN Function by David Hager

 

People in many walks of life need a list of instructions to follow. These can include from a doctor’s instruction, an exercise routine, a corporate protocol, etc. In many cases, a generic list of all possible instructions could be available. If so, the following technique could be very useful.

The list of instructions shown in column A is accompanied by a column B containing Yes or No (added through Data Validation). The desired result is to create a string that contains only the instructions that are matched with Yes.

The following array formula in G3 works for this example.

=TEXTJOIN(CHAR(10),,IF(B7:B16=”Yes”,A7:A16,””))

It will return a text string for each selected instruction to appear as a vertical list, which then can be printed to give to the intended group or person. See Figure:

 

There are a number of other useful variations of this technique which I plan to explore.

You can download the example file here.

TJ_Instruction

 

Run Your Power Query M Code Procedures in #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

Source = Excel.CurrentWorkbook(){[Name=”MCode”]}[Content],

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Storing and Running Power Query M Code from #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

Source = Excel.CurrentWorkbook(){[Name=”MCode”]}[Content],

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

 

The key to returning valuable information to Excel is to find sources of xml data. In this example, the xml data is supplied by

https://earthquake.usgs.gov

The goal is to make an alert message for the latest strong earhquake occurring worldwide. The following 3 formulas return the information needed for that message.

Location: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//description/text”)

Magnitude: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//mag/value”)

Time: =TEXT(FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//time/value”), “yyyy/mm/dd h:m”)&” UTC”

The WEBSERVICE function brings in the xml content from the web site and the FILTERXML function finds the desired data based on the node used in the 2^nd argument.

After these formulas return the desired data for the latest earhquake with a magnitude greater than 5.0, as shown in the figure

 

the TEXTJOIN function can be used to construct a readable alert message. The following array formula in cell B5 affords a formatted message from the cells A1:B3.

=TEXTJOIN(IF(COLUMN(A1:B3)=1,”: “,”; “),,A1:B3)

An important feature of this formula is the use of different delimiters based on the column where the information resides.

I hope that you find these techniques useful.

You can download the example file here.

earthquake_xml

 

Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager

In the following article:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

I demonstrated a method of obtaining unique items from a 3D range. At the time that this article was written, I was unaware that the TEXTJOIN function accepted native 3D references. See:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The techniques needed to convert a delimited string to a unique delimited string have already been discussed.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, with the starting point of a delimited string obtained by using 3D reference with TEXTJOIN, the link above will then allow you to create the unique array or delimited string.

To be clear, first TEXTJOIN is used with a 3D range argument to make a delimited string. Then, that string is used in the formula construction described in the link above to make an array of unique items, which can be subsequently used to make a unique delimited string.

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

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

it shows an example at the bottom of the article where the 1^st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:

=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).

As you can see in the figure, each record appears to be on a single line in the cell (F9).

This same technique can be enhanced further in this way.

=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula

=CHAR(10)&REPT(“-“,CELL(“width”,H9)+NOW()*0)&CHAR(10).

With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.

The example file can be downloaded here.

TJ_MultDelimiters

#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

In a conversation with Bill Jelen about the TEXTJOIN function, he mentioned whether I had tried using 3D references as arguments. I said that I had not tried native 3D ranges, and I did not believe it would work. Well, I was wrong (again). It does work, in spite of there being no documentation from Microsoft about that ability. The figure below shows the result in cell D2 of using this formula:

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2)

I continue to be amazed by the myriad of formula solutions made possible by the TEXTJOIN function. In a recent article where I demonstrated the use of non-contiguous ranges

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

I now realize that the same concept can be used with 3D ranges. (Here is the bonus!) In fact, that 3D range can be in ANOTHER workbook, as shown below.

=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2,[TEXTJOIN_Native3DB.xls]Sheet1:Sheet2!A1:A2)

The example file can be downloaded here (Note: I did not include the external file, and I put a tilde in front of that formula)

TEXTJOIN_Native3D

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

#Excel Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

you will find that there are multiple text arguments that are optional for TEXTJOIN. They have the same properties as the 3^rd argument, which allows for an array of strings. So, the following formula has 3 non-contiguous ranges as its last 3 arguments.

=TEXTJOIN(“,”,TRUE,A1:A10,C1:C10,E1:E10)

which affords

a,b,c,a,b,c,a,b,c,a,c,d,e,c,d,e,c,d,e,c,e,f,g,e,f,g,e,f,g,e

 

Note that this comma delimited string obtained from the TEXTJOIN function is the starting point of the technique demonstrated in this article.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, for example, you can use non-contiguous ranges as the starting point for making an unique array or unique delimited string. Pretty neat and powerful stuff!

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

 

Creating A Unique Delimited String From a Delimited String – Excel Formula Method With TEXTJOIN( David Hager)

Before I started my blog, I wrote an article about calculating the number of unique items in a delimited string. Dick Kusleika was kind enough to publish it on his web site.  Here is the link:

http://dailydoseofexcel.com/archives/2013/08/07/calculating-the-number-of-unique-items-in-a-delimited-string/

You know, It’s amazing what you can learn when you reread something you previously had written :). I was thinking of how to convert a delimited string into an array, and, lo and behold, I had already done this as part of that article. Here is that formula shown below:

DelStrArry=TRIM(MID(SUBSTITUTE(Sheet1!$A$1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:”&LEN(Sheet1!$A$1)-LEN(SUBSTITUTE(Sheet1!$A$1,”,”,””))+1))*999-998,999))

Armed with this formula (which creates an array) that can be used as the main argument in the TEXJOIN formula demonstrated in my previous blog post, I was ready to complete the desired formula. See:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

It was also necessary to correctly scale the row array the same size as the primary array. This was accomplished with the following defined name formulas.

CntDelStrArry=COUNTA(DelStrArry)

RowArry=ROW(INDIRECT(“1:”&CntDelStrArry))

The final result is:

=TEXTJOIN(“,”,,IF(MATCH(DelStrArry,DelStrArry,0)=RowArry,DelStrArry,””))

which will convert a delimited string like, for example

a,b,c,c,d,a,e,r,h,h,t,o,x,a,b,c  (in A1)

to

a,b,c,d,e,r,h,t,o,x

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