#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

There was a lot of interest in my post on modifying a shape with a worksheet UDF.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

The original idea was posted in 2007. I seem to remember, though, that the use of a UDF to modify cells occurred before that time. The initial discovery was that a UDF could add a cell comment to ANY cell. I can’t find the original reference, but this technique was last documented at:

http://www.listendata.com/2013/04/excel-udf-dependent-cell-comment.html

I have modified the the UDF shown in that article to add a timestamp feature.

Function AddComment(rng As Range, str As String) As String

If Not rng.Comment Is Nothing Then rng.Comment.Delete

TimeStamp = Date & ” ” & Time

If Len(str) Then rng.AddComment.Text str & ” ” & TimeStamp

rng.Comment.Visible = True

End Function

In the example workbook, I entered the AddComment function in cell D6, but the range argument can point to any cell. In fact “range formulas” can also be used.

The INDEX, OFFSET and INDIRECT Excel functions all return ranges, so any formulas built with these functions can be used in a UDF where a range argument is required. The following example uses the INDEX function.

=AddComment(INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)),”MAX value in NumRange”)

where NumRange is defined as =OFFSET(A$1,,,COUNTA($A:$A),) ‘auto-expanding range

In this example, the formula INDEX(NumRange,MATCH(MAX(NumRange),NumRange,0)) returns the range of the cell containing the max value of NumRange, and as such it can be used in the first argument of the UDF. So, as numbers are added to column A as shown in the figure

the function will add a timestamped comment to any cell in that range that is the max value.

Obviously, there are numerous and more complex examples that can be built using this technique. I hope that you will find this useful in your projects.

The example file can be downloaded here.

AddComment

Excel Short & Sweet Tip #7(Highlighting External Links) by David Hager

Using conditional formatting to highlight external links has been used before

https://www.extendoffice.com/documents/excel/1539-excel-highlight-external-links.html

but it required a VBA solution. Now, with Excel’s new FORMULATEXT function, it can be accomplished using only Excel formulas. So, using the following formula defined as IsExternalLink

=FIND(“[“,FORMULATEXT(D10))

Conditional formatting will highlight the cells containing “[“, which is associated with external link formulas. But, a more robust formula can also be used, as shown below.

=FIND(“[“,FORMULATEXT(D10))+FIND(“]”,FORMULATEXT(D10))

 

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

 

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0)

Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames

Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

The ability to randomly scramble a string using Excel formulas does not appear to be possible without helper cells. So, a VBA procedure is needed to accomplish this.

Originally posted at:

https://www.mrexcel.com/forum/excel-questions/37340-word-scramble.html

this VBA function procedure uses a string as the argument and shuffles that string. Copy/paste this procedure into a module in the VBE.

Function ShuffleString(s As Variant)

On Error Resume Next

Dim CL As New Collection

Application.Volatile

ShuffleString = “”

Do Until CL.Count = Len(s)

R = Int(1 + Rnd * Len(s))

CL.Add R, CStr(R)

Loop

For i = 1 To CL.Count

ShuffleString = ShuffleString & Mid(s, CL(i), 1)

Next

End Function

So, the string in A1 is rearranged with =ShuffleString(A1) entered on the worksheet. For example, the string “evert” is shuffled to “rteve”.

#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.

=SUM(Sheet1:Sheet2!B2:B5)

So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:

=SUM(C2:C5,Sheet2!C2:C5)

So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.

=MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:

=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:

=IFERROR(MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)<=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0),FALSE)

Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.

You can download the example file here.

IsNative3DFormula

Excel Short & Sweet Tip #5 (Hiding Error Triangles) by David Hager

Would you like a way to remove those green error checking triangles from worksheet cells, yet retain error checking? Go to File, Options, Formulas, Error Checking. Leave the “Enable background error checking” box checked, and change the Indicate errors using the color (default is green) to white. Of course, this only works if the cells are white. To turn off the error checking and the green triangles, change the “Enable background error checking” box to unchecked.

 

Excel Short & Sweet Tip #4 (Hide Defined Names)

If you use a lot of defined name formulas, you might want to keep them from from being accidentally altered. You can run this procedure from a module in the VBE to accomplish this.

Sub HideDefinedNames()

For Each rname In ActiveWorkbook.Names

rname.Visible = False

Next

End Sub

#Excel Short and Sweep Tip #3 (Freeze Values) by David Hager

Highlight a worksheet range that contains formulas or a mixture of formulas and values. Grab the edge of the range with right-click and pull away, then immediately place back in the same range location. The right-click menu will appear. Click the option labelled “Copy here as Values only”. Every formula in the range will be converted to its underlying value.

 

#Excel: Modifying Shapes From An UDF in a Worksheet Cell by David Hager

It is well-known that an used-defined function created with VBA and entered as a worksheet formula cannot alter the worksheet. But, it is not true. A number of examples where this can occur have been discussed and documented. In particular, a post by John Walkenbach referred to the altering of worksheet shapes (see link below) with an UDF.

http://dailydoseofexcel.com/archives/2007/01/12/modifying-shapes-and-charts-with-udfs/

Since this technique has not gained popularity in use, few people know about it. So, I have made an user-friendly model to demonstrate its utility.

The biggest deficiency in working with shape properties is the lack of a good source of lookup information on what the index numbers refer to. I iterated (manually) through all 181 index numbers for shape type (assigned the shapes names based on what I decided, they can be changed is desired), and the table I created can be seen in the ShapeLists worksheet in the example workbook. I defined a list of shape names and used it in a Data Validation List for cell B2. Then, in cell B3, this formula looks up the correct shape index number for the selected shape. That result is used by the ModifyShape UDF in cell G2.

=INDEX(ShapeIndex,MATCH($B$2,ShapeName,0)) ‘in B3

=ModifyShape(1,$B$3) ‘in G2

 

Here is the ModifyShape VBA function.

 

Function ModifyShape(ShapeNumber, ShapeType, Optional Vis As Boolean = True)

With ActiveSheet.Shapes(ShapeNumber)

.AutoShapeType = ShapeType

.Visible = Vis

End With

End Function

Note that it has an optional 3^rd argument. It is TRUE by default, but if is set to FALSE, the shape is invisible. And, all of this occurs simply by the recalculation of the UDF.

There are a number of shape properties that can be manipuated by an UDF. One fuction that I specifically designed for this article is the ModifyShapeColor function.

Function ModifyShapeColor(ShapeNumber, rColor, gColor, bColor)

With ActiveSheet.Shapes(ShapeNumber)

.Fill.ForeColor.RGB = RGB(rColor, gColor, bColor)

End With

End Function

But, for this example to be fully effective, I needed a reference table of RGB colors that could be utilized by this function. I was able to find such a table at this source:

http://rapidtables.com/web/color/RGB_Color.htm

and I pulled this information into the workbook by using Power Query. Then, I copy/pasted it to the ShapeLists sheet and deleted the query (no need to refresh static information). I defined a list of shape colors and names and used the Shape color name in a Data Validation List for cell C2. Then, in cells C3:C5, these formulas look up the correct RGB numbers for the selected color.

=INDEX(Red,MATCH($C$2,ColorName,0)) ‘in cell C3 etc.

=ModifyShapeColor(1,$C$3,$C$4,$C$5) ‘in cell G3.

You can see the resulting shape and color in the following figure.

Now, realize that the worksheets can have multiple shapes controlled by the UDFs pointing at each shape. This opens up a new way to visualize data. I hope that you can incorporate this technique into your projects.

You can download the workbook here:

ModShape

#Excel Short & Sweet Tip (Find Formulas)#2

With the active cell at A1, create a defined name formula in Name Manager called IsFormula using this Excel formula:

=LEFT(FORMULATEXT(A1),1)=”=”

Then, apply a conditional format to A1 as shown below:

Then, you can Copy/Paste Special/Formats to the region containing formulas.

Excel Short and Sweet Tip #1 (Alphabetic String With TEXTJOIN) by David Hager

In many cases an alphabetic string is needed in certain Excel formula constructions. The easiest way to generate this string is with the following:

=TEXTJOIN(“”,,CHAR(64+ROW(1:26))) ‘ array-entered

which affords the string

“ABCDEFGHIJKLMNOPQRSTUVWXYZ”

Other character sets strings (such as alphanumeric) can be made in a similar manner.

 

 

#Excel: Four Super Filter Techniques by David Hager

I wanted to improve a few design features and eliminate a few bugs in some of my related recent posts,

so I have combined the concepts demonstrated by the following 4 articles located at:

https://dhexcel1.wordpress.com/2017/03/30/excel-filtered-list-using-a-calculated-column-to-count-occurrence-number-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-creating-a-list-made-up-of-formulas-from-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

into a single workbook. Among the changes I did/did not make are:

1. No change to the formula that given occurance number for records in a filtered list.
2. I moved the formula list from above the filtered list to another worksheet.
3. I fixed some issues with the VBA code for the Filter Criteria UDF. I then located it with the
4. the static list on the other worksheet.
5. I included the Advanced Filter search with a custom list with this model.

In moving the static calculated list to another worksheet, I had significant problems adjusting the formulas to work in their new location. For the formula:
=INDIRECT(“Sheet1!a”&SMALL(IF(SUBTOTAL(3,OFFSET(Sheet1!$C$24:$C5023,ROW($C$24:$C5023)-MIN(ROW($C$24:$C5023)),,1)),ROW(1:5000),””),ROW()-2)+26-ROW())

I had to add the sheets names in the parts of the formula that point to the filtered list on Sheet1. Also, to create the correct positioning of the first formula(s) in the list (place on row 3 instead of row 2), I had to change:

ROW()-1)+25-ROW())

to

ROW()-2)+26-ROW())

The FilterCriteriaEnh function was amended to fix several faults – an error handler was added to fix the scenario where .Criteria2 did not exist and adding a line of code (Criteria2=.Criteria2) in case it did (see code in the example workbook).

The criteria UDF was relocated to the row above the static list. The following formula in A1 on the Static worksheet is:

=IFERROR(FilterCriteriaEnh(Sheet1!A23:A5023),””)

Note that the range in the UDF argument points at the filtered list on Sheet1.

The list used for the advanced filter lookup is also located on the Static worksheet. As expected, when I activated the advanced filter the filter criteria UDF did not return a result, since no “filter” was applied to the list.

I hope that I have explained what is available with this new filter model, but if not, please go back and reread the 4 base articles.

You can download the (enhanced) file here.

FilteredResultsTable_AdvancedFilter

 

#Excel: Using Advanced Filter with a Custom List By David Hager

In a previous article about Excel’s advanced filter,

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

I used a reference to Charley Kyd’s use of SUMPRODUCT to filter a list based on a custom list.

http://www.exceluser.com/formulas/search-list-with-sumproduct.htm

In his article, he created a formula used in a helper column to filter based on a custom list of criterias.

I realized that the same type of formula could be used in Excel’s advanced filter. A lot of the utility of using the Excel advanced filter feature is hindered by the fact that an array formula cannot be used as a criteria. However, since the SUMPRODUCT function creates a non-array formula, I surmized that it could be used as a criteria.

BTW, Rob Collie and I published an article on this same subject in PowerPivot a few years ago.

https://powerpivotpro.com/2011/04/calculating-a-sum-based-on-a-list-criteria/

In this scenario, a list containing a column of employee numbers needs to be filtered by a custom subset of those numbers. This could be done by manually selecting those numbers from the filter dropdown criteria, which would take a very long time for a large custom list. The custom list resides on a different worksheet than the list to be filtered (in this case, on the List worksheet defined as EmployeeList).

The criteria formula (in B2) is:

=SUMPRODUCT(NOT(ISERROR(SEARCH(EmployeeList,A5)))+0)>0

Note that this formula “starts” at A5, the first item in the column.

Before applying the filter, the column looks like this:

 

The advanced filter information is set up as shown below.

 

Finally, the list after filtering looks like this:

 

In this example, there is only one column in the list/table. Obviously an employee table would contain many more columns. Also, the employee custom list could potentially could contain 1000’s of employee numbers. I hope that you use this technique useful in your work on employee records.

You can download the example file here.

MultAdvFilter

 

#Excel VBA: Create a Table of File Locations and URLs for Your Favorites by David Hager

This is one of my personal Excel applications that I have used over the years to collect information on my Internet Favorites. When you run the CreateURLListFromFiles procedure, it creates a list of URLs and favorites file location. You browse to your Favorites folder and select to generate the table. Column B contains the URLs that can be converted to hyperlinks by selecting them and running the Convert To Hyperlinks procedure. You can use this on multiple folders where your favorites are stored and the results will be appended. Enjoy!

You can download the file here.

MyURLCollector

#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

Mine3D for #Excel: An Excel-based Game by David Hager

I would like to share with you an Excel game that I made back in the day. It is called Mine 3D, and it works like the popular minesweeper game, except that the playing board is “3 dimensional”. The worksheets are protected, but with no password. There is a interesting combination of formulas and programming used in the making of this game. So, feel free to explore how it works. Enjoy!

You can download the Excel game here.

Mine3D 

 

 

Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists by David Hager

So, imagine a scenario where 4 filtered lists reside in the same position on adjacent worksheets that have “sequential” names. Perhaps each list comes from a monthly summary, but filtering is applied differently for each list. Well, I did not build that example, but instead have 4 sheets named A to D, a list on each sheet residing in the same range and each column filled with numbers from 0 to 99. The object is to perform aggregations on the filtered data from all sheets at once (or, creating a 3D formula).

Actually, I have already written a blog post about this subject, but I felt that it would be useful to cover this again in an expanded version and provide a workbook to download.

https://dhexcel1.wordpress.com/2014/06/13/using-excel-3d-formulas-on-filtered-lists/

For examples on various types of “multi-worksheet” formulas, see:

https://dhexcel1.wordpress.com/2014/06/09/excel-3d-easy-as-1-2-3-and-a-b-c-and-others/

The figures below shows the results of the 2 types of 3D formulas.

The filter-enabled formula shown below sums the filtered values in the 4 filtered lists. Note that since the SUMPRODUCT function is used here, the result is NOT an array formula.

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)))

The native Excel method for creating a 3D formula is shown here.

=SUM(A:D!C2:C21)

The result from this formula is a number much higher than the filter 3D formula since there is no way to use the native 3D referencing to construct a filtered result.

Other types of aggregation from the filtered 3D formulas, but they must be constructed as array formulas. The following formulas show the filtered MIN for the desired range and the corresponding Excel-centric 3D formula.

=MIN(SUBTOTAL(5,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”))) ‘array formula

=MIN(A:D!C2:C21)

This example shows the formulas for the MAX 3D-filtered and Excel 3D.

=MAX(SUBTOTAL(4,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)) ‘array formula

=MAX(A:D!C2:C21)

There are obviously a number of variations on this theme possible. Knowing that this type of aggregation is available could possibly change the way you analyze your data. HTH!

You can download the file here.

Excel3DSubtotal

 

Using #Excel VBA to Create a Filter Criteria User Defined Function by David Hager

Some of the very best Excel work in the early years was done by Stephen Bullen. His legacy Excel site still exists, but it has not been updated in many years.

http://www.oaltd.co.uk/Excel/Default.htm

One of Stephen’s creations was a “simple” VBA function to return the applied criteria of a filtered list to a worksheet cell (shown below).

 

Function FilterCriteria(Rng As Range) As String

‘By Stephen Bullen

Dim Filter As String

Filter = “”

On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & .Criteria2

Case xlOr

Filter = Filter & ” OR ” & .Criteria2

End Select

End With

End With

Finish:

FilterCriteria = Filter

End Function

The use of this function is illustrated in the following figure. The formulas are in row 1.

Here is another view with criteria applied to other columns.

It also shows a limitation that this function had. Since the .Criteria1 and Criteria2 properties are strings, when criteria is set for a date column, the string contains Excel’s “date number”, not the date formatted number displayed in the cells. In order to workaround this limitation, I amended the function as shown below.

Function FilterCriteriaEnh(Rng As Range) As String ‘Enhanced to handle date filters

‘By Stephen Bullen and David Hager

Dim Filter As String

Dim Criteria2 As String

Filter = “”

sFormat = Application.Index(Rng, 2).NumberFormat

‘On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

End If

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & Criteria2

Case xlOr

Filter = Filter & ” OR ” & Criteria2

End Select

End With

End With

Finish:

FilterCriteriaEnh = Filter

End Function

 

Function OnlyDigits(s As String) As String

With CreateObject(“vbscript.regexp”)

.Pattern = “\D”

.Global = True

OnlyDigits = .Replace(s, “”)

End With

End Function

 

First, I needed to capture the format from the column is question to see if it was date formatted.

sFormat = Application.Index(Rng, 2).NumberFormat

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

The change in the string for the Filter variable is made by the formula shown above. The OnlyDigits function used in the formula construction is not original, but I do not know the source. It puts the string back together with the date replacing the date system number.

I did the same thing for Criteria2, but it will not exist if a second criteria is not selected in the filter, so I had to add error handling for that scenario.

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

A final filter list example using this enhanced function is shown below.

You can download the file for this here.

FilterCriteria

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

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,””))

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.

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

MultipleLookupValues

#Excel Shift Calendar by David Hager

I built this shift calendar workbook years ago, but I never shared it online. You can name the shifts in G4:G5. You can use the ShiftDataEntry sheet to change the shifts if desired. Shift1 is known as the Dupont shift. Shift2 is known as the EOWEO shift. The app works by using a large number of defined names. Use it as intended. The download link is below.

Dual_Shift_Calendar

 

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.

 

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 2^nd 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