Google Sheets ArrayFormula Sort & Split Issue

0
votes

Have been researching several places for a solution and found something that quasi works but doesn't work in the ArrayFormula for Sheets. I have data in a single column as an example below. It's a combination of letters and text but would otherwise been sortable when parsed. The goal is to populate the cell with the single most recent event (as measured by the highest number at the end of the string). The value "Sprint" is consistent text. This formula seems to work for that purpose, however, I would ideally like this to work within the ArrayFormula so that it would adjust and populate with the data rows it's referencing which is dynamic. Thanks in advance.

TRIM(CHOOSE(1,SPLIT(JOIN(";",SORT(TRANSPOSE(SPLIT([@CELL],";",FALSE)),1,FALSE)),";")))

Below is a sample set of data:

Sprint 1
Sprint 2
Sprint 3
Sprint 3;Sprint 1
Sprint 1;Sprint 2
1
google-sheetsmaxgoogle-sheets-formulaarray-formulasgoogle-sheets-query
share a copy of your sheet with example of desired output – player0
The output as described is a single column with the single highest value and looks like the below and adjusts to the size of the data as the originating data column increases or decreases. Will try to share a sheet but the description is fairly straightforward. Sprint 1 Sprint 2 Sprint 3 Sprint 3 Sprint 2 – jbm
Link to sheet with output Column A and input Column B docs.google.com/spreadsheets/d/… – jbm
Also please keep in mind the example I've provided is showing two values in some cases with the delimiter. There could very well be 3 or 4 in a single field sometimes. – jbm
Hello @jbm, would you mind posting a sheet which contains the desired result as well? Cheers! – ale13

1 Answers

1
votes

try:

=ARRAYFORMULA(B1&" "&QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 REGEXREPLACE(IFERROR(SPLIT(B2:B, ";")), "\D+", )*1),
 "select "&TEXTJOIN(",", 1, IF(B2:B<>"",
 "max(Col"&ROW(B2:B)-ROW(B2)+1&")", ))&"")),
 "select Col2"))

0

UPDATE:

=ARRAYFORMULA(IF(REGEXMATCH(B2:B, B1), 
 B1&" "&QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 REGEXREPLACE(IFERROR(SPLIT(B2:B, ";")), "\D+", )*1),
 "select "&TEXTJOIN(",", 1,
 "max(Col"&ROW(B2:B)-ROW(B2)+1&")")&"")),
 "select Col2"), ))

0