[ Index ]

remove_non_matches

This spreadsheet finds all text strings matching a given pattern. It uses the same style of recursive programming as the science-fiction generator, but this time applied to a "real-world" problem. This is something that came up in one of my consultancy-with-Excelsior jobs, when I had to create several sublists of dropdown options from a master list. Each sublist had to contain only those elements that matched a given pattern. This is difficult to code directly in Excel, but pretty easy in Excelsior.

Inputs

These are the cells to search for occurrences of the pattern pattern, and pattern itself:

constant pattern.

table elements_to_search : elements_base -> text.

Outputs

Element matching_elements[i] is the i'th element of elements_to_search that matches pattern, or blank if there are no more such elements.

table matching_elements : elements_base -> text.

Workings

Element the_index[i] is the index of the i'th element of elements_to_search that matches pattern, or -1 if there are no more such elements.

type elements_base.

table the_index : elements_base -> text.

the_index[ 1 ] =
  if( isna( match( pattern, elements_to_search[all], 0 ) )
    , -1
    , match( pattern, elements_to_search[all], 0 )
    ).

the_index[ i > 1 ] =
  if( the_index[i-1] = -1
      // There was no previous one, so can't be another.
    , -1
    , if( the_index[i-1] = upb(elements_base)
          // Previous one was at the end of the table,
          // so there can't be any more.
        , -1
        , if( isna( match( pattern
                         , elements_to_search[ (the_index[i-1]+1):upb(elements_base) ]
                         , 0
                         )
                  )
            , -1
            , match( pattern
                   , elements_to_search[ (the_index[i-1]+1):upb(elements_base) ]
                   , 0
                   ) + the_index[ i-1 ]
            )
        )
    ).

matching_elements[ i ] =
  if( the_index[ i ] <> -1
    , elements_to_search[ the_index[i] ]
    , ""
    ).