New sort functions and sorting to groups

Posted on 16/05/2020 by dougaj4

I first posted a user defined function (UDF) to sort spreadsheet or VBA data back in 2009.  Finally Microsoft have caught up, and Excel 365 subscribers now have two built in sort functions available:

=SORT(array,[sort_index],[sort_order],[by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

As an example of using the new functions I have looked at options or sorting a list into groups from Eng_Tips:

I have a long list of unique data in excel in the following form. As a first step I removed all the duplicates.
The unique headings always start with a “$_”. There are many more unique headings in the list other than GRIDS and MATERIALS.
I would like to filter the Original List, such that each data entry under a unique heading is merged under a single heading.

The examples shown below, including open source VBA code, can be downloaded from:
GroupSort.xlsb

The screenshots below show two options for performing this sort, using the Sort and Sortby functions:

The data to be sorted consists of group names, preceded by a $, followed members of the group.  To sort the data into continuous lists for each group requires a “helper column” showing the current group name for each row (see cell B4).  The data can then be sorted first by Column B, then Column A.  The sort function returns both columns, and every instance of the group name (and also every instance of group members, if these have been duplicated).

Tidier results can be returned using Sortby:

The Sortby function returns only the data to be sorted (specified in the first argument), and allows any number of “sort by columns”.  In this case Column B then Column A are specified.  The Unique function has been added to remove all duplicate elements.

For those who don’t yet have the sort functions, and to avoid the need for a helper column, I have written a GroupSort UDF, as shown below:

By default, GroupSort returns a 2D array, with one column for each group.  Setting the optional “ReturnArray” argument to False returns a single column, with duplicate group names removed.  Full open source code is included in the download file.

Another example using the Sortby function is shown below, to sort a list of names by the second name.  In this case the formula FIND(” “,A48:A71)+1,6) returns an array of the second names, which can be used as the data to sort on, without needing a helper column:

 

Posted in Excel, UDFs, VBA | Tagged , , , , , | Leave a comment

Lennox Bridge

Posted on 08/05/2020 by dougaj4

Last December I joined Bill Harvey on a visit to Lennox Bridge west of Sydney.  Bill has now documented the visit in his Bridge of the Month site, including history, comments on the structural form, and a dynamic 3D model of the structure:

Lennox Bridge is an integral part of Mitchell’s Pass, the old road into the Blue Mountains (I presume through them as well, but I didn’t get that far). It is the oldest surviving stone bridge in mainland Australia (an older one off the mainland is in Richmond, Tasmania, and was opened eight years earlier in 1825

Visit Lennox Bridge for more details, photographs, and view the 3D model at https://skfb.ly/6RZEx .

One of my photos, with Bill constructing his 3D model:

 

Posted in Arch structures, Historic Bridges, Newton | Tagged , , , , | Leave a comment

The Concrete Institute of Australia – Golden Jubilee

Posted on 25/04/2020 by dougaj4

The Concrete Institute of Australia will celebrate 50 years in 2020. The Institute was originally formed when members of the Australia Prestressed Concrete Group proposed a change in 1968 where it was resolved to adopt the name Concrete Institute of Australia. This occurred in May 1969 and the first meeting of Council was then held in July 1969 which was chaired by Mr William (Bill) Brown who was elected as the first National President. Following this the Institute was officially registered as a company limited by guarantee with approved Memorandum and Articles of Association on 17th April 1970.

Posted in Concrete, Newton | Tagged , | Leave a comment

The String Family

Posted on 23/04/2020 by dougaj4

According to their web site, The String family are:

A family of classically trained string players who launched their musical career by hitting the road full time from 2016 through 2018, bringing music to people around Australia. Featuring 2 cellos, 2 violins, voice and percussion they incorporated their unique story of life on the road as Australian musical gypsies with toe tapping celtic, folk, world and classical crossover music. On their musical journey they received numerous awards for performance and songwriting including: New Australian Celtic Talent of the Year, Australian Celtic Song of the Year, Australian National Busking Champions.

Recent conditions have put a stop to their travelling, but not their music making:

 

Posted in Bach | Tagged , | Leave a comment

Getting near matches from a list with Python

Posted on 19/04/2020 by dougaj4

To return a list of near matches from a long list of strings would be quite difficult in VBA (unless I’m missing something), but in Python it is very simple.  Here is the code for this operation, using pyxll to read a list from Excel, and return the near matches as the result of a User Defined Function (UDF):

from difflib import get_close_matches

@xl_func
@xl_arg('patterns', 'str[]')
@xl_arg('n', 'int')
def closeMatches(patterns, word, n =3, cutoff = 0.6): 
     return get_close_matches(word, patterns, n, cutoff)

The function required arguments are:

  • patterns: A single column list of text strings
  • word: A single word to find close matches for in the list.

By default the function returns a maximum of 3 results.

The example below looks for matches to “py_GetBeamSection” in the list of 1800+ function names in column R, returning 3 matches:

The first optional argument, ‘n’, specifies the maximum number of strings to return, set to 30 below:

This list can be reduced with the second optional argument, ‘cutoff’, which has a default value of 0.6.  Increasing this to 0.75 reduces the number of matches to 11 in this case:

Note that for the particular application of finding function names the Excel function wizard also does a pretty good job.  Entering ‘=py_GetBeamSe’ returns a list of the 7 closest available functions (all UDFs written in python in this case):

Select the function you want from the list and press tab, and the function will be entered on the edit line.  Then click the ‘Insert Function” icon (top left in the screen-shot below), and the function is listed together with a list of arguments, and where available, the function and argument descriptions included in the code:

Posted in Excel, Link to Python, PyXLL, UDFs | Tagged , , , , , | Leave a comment