Search code examples
arraysgoogle-sheets-formulaflattengoogle-query-languagetextjoin

Sort multi-line cells containing dates in descending order in Google Sheets

In this shared sheet with multiple lines in one individual cell, I am trying to display the formula result by date by descending date order (ie most recent date first 2022, 2021 etc) inside of each cell.

Two related goals are shared in Columns B & D.

enter image description here

The starting data includes four variables: One, two, three, four, and each may have a variable value (ex: One: A) while a value for variable name One is always required and present, values may not be present for variables two, three and four. They can be blank.

In addition to sorting by most recent date, the formulas will also

  1. remove the variable names and trailing colon and space (ex: "One: ")leaving only variable values.
  2. insert &, on between variables (ex One & two three on four)
  3. remove extra white spaces or commas that result when values are not present for two, three, four. These are displayed in A5:A7

Goal #1: Starting data is displayed in Column A & the Goal for this data is displayed in Column B.

Goal #2: Same starting data in Column A, and Goal in Column D. This is similar to the above with one extra function that reorders the string so taht the date is placed in the beginning of the string (as a result the word "on" is not inserted)

The following formula in C2 has taken me part of the way for Goal #1 except it did not sort by most recent date

ARRAYFORMULA(TRIM(SUBSTITUTE(REGEXREPLACE(  
SUBSTITUTE
(SUBSTITUTE
(SUBSTITUTE
(SUBSTITUTE
(SUBSTITUTE
(SUBSTITUTE(
A2:A10
,"One: ",""), ", two: ", " "&CHAR(64)&" "), ", three: "," "), ", four: "," on "),"four:","")," "&CHAR(64)&"  ,",""),
 "\s\s"," "),",","")))

But I'm not sure how I would go about sorting this dataset in descending order inside of a cell.

I do think that it might involve Splitting the dates and I think an alternative function of the formula would reorder the string so that the date appears in the front of each line instead of at the end.

The shared sheet is here: https://docs.google.com/spreadsheets/d/1pDqt-jfwmvo7bYuenjcWhrKJh6iqGF6pajUd6egCj0I/edit#gid=0

Thank you in advance for your help!

Solution

  • try:

    =ARRAYFORMULA(IF(A2:A7="",,SUBSTITUTE(REGEXREPLACE(TRIM(FLATTEN(
 QUERY(QUERY(QUERY(SPLIT(FLATTEN("♥"&SUBSTITUTE(TRIM(SPLIT(
 REGEXREPLACE(REGEXREPLACE(A2:A7, ", four: ", " on "), "(?:)(?i)(one|, two|, three|, four):", ), 
 CHAR(10))), " ", " @ ", 1)&IF(IFERROR(SPLIT(A2:A7, CHAR(10)))<>"", "×"&ROW(A2:A7)&"×", )&
 IFNA(REGEXEXTRACT(SPLIT(A2:A7, CHAR(10)), "\d+/\d+/\d{4}"))), "×"), 
 "select max(Col1),Col3 where Col2 is not null group by Col3 pivot Col2 order by Col3 desc "), 
 "offset 1", ),,9^9))), "^♥", ), "♥", CHAR(10))))

    enter image description here

    update:

    =ARRAYFORMULA(IF(A2:A7="",,TRIM(SUBSTITUTE(REGEXREPLACE(TRIM(FLATTEN(
 QUERY(QUERY(QUERY(SPLIT(FLATTEN("♥"&IFNA(REGEXEXTRACT(SPLIT(A2:A7, CHAR(10)), "\d+/\d+/\d{4}"))&" "&SUBSTITUTE(TRIM(SPLIT(
 REGEXREPLACE(REGEXREPLACE(A2:A7, "(?:)( \d+/\d+/\d{4})", ), "(?:)(?i)(one|, two|, three|, four):", ), 
 CHAR(10))), " ", " @ ", 1)&IF(IFERROR(SPLIT(A2:A7, CHAR(10)))<>"", "×"&ROW(A2:A7)&"×", )&
 IFNA(REGEXEXTRACT(SPLIT(A2:A7, CHAR(10)), "\d+/\d+/\d{4}"))), "×"), 
 "select max(Col1),Col3 where Col2 is not null group by Col3 pivot Col2 order by Col3 desc "), 
 "offset 1", ),,9^9))), "^♥", ), "♥", CHAR(10)))))

    enter image description here