Skip to main content

This is a basic hack to sort a simple list of pre-determined roll-up values (e.g. 12 months) without a script or 3rd party automation.

In the example below, month values from the Sub-items table are rolled up in Items table / Unsorted column.  Columns 'M1', 'M2', 'M3' use a REGEX_MATCH formula to check 'Unsorted' for the corresponding values.  The column 'Sorted' uses a series of IF statements to to join these together in the right order.

 

 

What formulas are you using?

What formulas are you using?


  

Cool, thanks for posting the formulas!

Just an FYI that while REGEX works in this example, you wouldn't want to use REGEX if you're searching for words that have special characters in them (such as ?, +, *, etc.). That's because certain characters would be interpreted as REGEX instructions.

Since you're just performing a normal search, you can use FIND() or SEARCH().

For example:

IF(
FIND( "Jan", {Rollup Field} & "" ),
"Jan")

 

And then, if you're so inclined, you can reduce the multiple formulas down to just one unified formula like this:

IF(
FIND( "Jan", {Rollup Field} & "" ),
"Jan, ")

&

IF(
FIND( "Feb", {Rollup Field} & "" ),
"Feb, ")

&

IF(
FIND( "Mar", {Rollup Field} & "" ),
"Mar ")

As you mentioned, this trick would only work with a predefined list of values, and then you may want to create another formula to take care of the lingering comma at the end if the final value doesn't exist.

Reply


Terms & Conditions