Monthly Archives: August 2017

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”.

FormulaTextByFunction1

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

FormulaTextByFunction2

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

Advertisements

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

TJ_3dTo1dArray1

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:

 FormulaText_TEXTJOIN1

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 3rd 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.

 IngredientListByCost1

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!

Creating a Priority List With #Excel by David Hager

 

In my previous article I demonstrated a technique to show a list based on priority.

https://dhexcel1.wordpress.com/2017/08/04/list-of-priority-items-from-another-list-using-excel-textjoin-function-by-david-hager/

Since I wanted the solution to be a single cell, single formula technique, the calculation was somewhat complex and the TEXTJOIN function was used to create the string. For that technique, you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work. But, many people do not have access to that Excel version. So, I decided to publish a solution that does not require TEXTJOIN. Although this method uses several intermediate steps, in many ways it is much simpler to implement.

In the list are columns defined as Item and Priority respectively. To obtain the priority list, place consecutive numbers from 1 to 5 (per the values in Priority) in column C starting at C2. Then, put the following formula in cell D2 and fill down.

=INDEX(Item,MATCH(D2,Priority,0))

That gives the priority list in column D in cells. If you want to reproduce the text string as per the previous article, place this formula in cell F14 as shown in the figure.

=E2&CHAR(10)&E3&CHAR(10)&E4&CHAR(10)&E5&CHAR(10)&E6

TJ_Item_Priority_List1

You can download the example file here.

TJ_Item_Priority_List

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.

TJ_Item_Priority1

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.

TJ_Item_Priority2

Note that the 1st 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