Excel - Sorting textual versions using dynamic arrays

Question

I need to sort versions of software, and like you can see in this picture, the source is in 6 colomns (it can change), stored in a dynamic array.

I need only one colomn with the higher versions in numbers, more or less like i tried to do in the two last columns. Is two different try to get the output, without succes.

Here the formula :

=LET(
    _a; ESTNUM(CHERCHE("7.";A1#));
    _b; ESTNUM(CHERCHE("8.";A1#));
    _c;SI(_a+_b;A1#;"");
    _d;BYROW(_c;LAMBDA(x;CHOISIRLIGNES(TRIER(TRANSPOSE(x);;-1);1)));
    _e;BYROW(_c;LAMBDA(x;CHOISIRCOLS(FILTRE(x;x<>"";"");1)));
    ASSEMB.H(_d;_e)
)

In english :

=LET(
    _a, ISNUMBER(SEARCH("7.",A1#)),
    _b, ISNUMBER(SEARCH("8.",A1#)),
    _c, IF(_a+_b,A1#,""),
    _d, BYROW(_c,LAMBDA(x,CHOOSEROWS(SORT(TRANSPOSE(x),,-1),1))),
    _e, BYROW(_c,LAMBDA(x,CHOOSESCOL(FILTER(x,x<>"",""),1))),
    HSTACK(_d,_e)
)

here _d, i my best result I think, but not perfect. Have you any idea to reach my goal ?

I'm using O365 and dynamic arrays

Are the version numbers always in their own cells? Are there sometimes more than 2 numbers in a given row? would a solution with helper columns be OK? — cybernetic.nomad
– cybernetic.nomad, Commented Jun 23 at 15:14
Are the version numbers always in their own cells? --> YES // Are there sometimes more than 2 numbers in a given row? --> the problem is here, when I have two number version, in the same row, I can't get the highest one // would a solution with helper columns be OK? --> be free to use all the colomns that you need, it's a workingsheet. The unique requirement is to use formula and dynamic arrays with # — MonkeyJLuffy
– MonkeyJLuffy, Commented Jun 23 at 15:24

Spectral Instance · Accepted Answer · 2025-06-24 04:48:58Z

1

This approach involves using regex repeatedly:

=REGEXREPLACE(LET(dat,LET(dat,
REGEXREPLACE(A1#,"(?<=\.)(\d)(?=\.)","0$1"),
IF(NOT(REGEXTEST(dat,"\.")),"",dat)),
BYROW(dat,LAMBDA(r,@SORT(r,,-1,-1)))),
"\b0(\d)\b","$1")

or

=REGEX.REMPLACER(LET(dat;LET(dat;
REGEX.REMPLACER(A1#;"(?<=\.)(\d)(?=\.)";"0$1");
SI(NON(REGEX.TEST(dat;"\."));"";dat));
BYROW(dat;LAMBDA(r;@TRIER(r;;-1;-1))));
"\b0(\d)\b";"$1")

The functionality is used

once to insert a leading 0 where appropriate
a second time to empty all cells not containing a . character, meaning that the version numbers can then be sorted
a third time to remove the leading 0s previously added

edited Jun 24 at 4:48

answered Jun 23 at 21:33

Spectral Instance

2,7792 gold badges5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MonkeyJLuffy Jun 24 at 7:42

Whoooo!!!! Perfect !! And thank's for the french formula. It was just perfect. Copy Past and Done !!!! Merci beaucoup :D

MonkeyJLuffy Jun 24 at 7:52

Obviusly a good knoledge of regex pattern is usefull, it's hard but I can understand it. The unique formula that I have more difficulty to understound, is the @SORT. Do you use it to get the first cell of the SORT return function ? Could you explain quickly the @ operator, thanks. If it is, is very smart. Nice Job !

Spectral Instance Jun 24 at 15:22

Yes, the @ returns only the first cell from each sorted row (this avoids the nested-arrays problem of using BYROW/SORT) (my IF() function was "too negative", i.e. SI(REGEX.TEST(dat;"\.");dat;"") is better)

Black cat · Accepted Answer · 2025-06-23 15:24:10Z

0

This is a specific formula with four pieces of max. two digit length numbers separated with dot.
Input range is A1:F1 can be changed to desired length of a row.
Drag down.

=LET(inp,A1:F1,
twodigit,BYCOL(inp,LAMBDA(bem,REDUCE("",TEXTSPLIT(bem,"."),LAMBDA(y,x,IF(LEN(x)=1,y&"0"&x,y&x))))),
twodigit2,IF(VALUE(twodigit)>0,twodigit,"0"),
sorted,TAKE(SORT(IFERROR(twodigit2,"0"),,-1,TRUE),,1),
ins,INDEX(inp,MATCH(sorted,twodigit2,0)),
ins)

First separate by dot the numbers and,
extend every value to two digit long (twodigit),
eliminate text (twodigit2) and then,
get the max value as a number (in sorted).
After look it up in the actual row, and returns the corresponding original value in it.

answered Jun 23 at 15:24

Black cat

7,6358 gold badges69 silver badges99 bronze badges

3 Comments

MonkeyJLuffy Jun 24 at 7:39

the twodigit response give me #value error. "The given data is incorrect" [twodigit;BYCOL(_c; LAMBDA(bem; REDUCE("";FRACTIONNER.TEXTE(bem;".");LAMBDA(x;y; SI(NBCAR(x)=1;y&"0"&x;y&x)))))]

Black cat Jun 24 at 7:47

You invert x, and y in the LAMBDA expression.

MonkeyJLuffy Jun 24 at 7:55

Even I change it, it give me the same error

cybernetic.nomad · Accepted Answer · 2025-06-23 15:26:13Z

0

This may work for what you need:

=INDEX(FILTER(A2:C2,ISNUMBER(--LEFT(A2:C2,1))),COLUMNS(SORT(FILTER(A2:C2,ISNUMBER(--LEFT(A2:C2,1))),)))

What it does is extract all cells with numbers into an array, then sort these in ascending value, then returns the last value from the sorted array

Note that it may break down is the name of a software starts with a number. It also will not extract version numbers from a cell containing both software names and version numbers.

answered Jun 23 at 15:26

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

2 Comments

MonkeyJLuffy Jun 24 at 8:01

This give me #Value! error. Could you explain the '--' operator ?

cybernetic.nomad Jun 25 at 13:09

-- will convert a string consisting of numerals (1,2,3,4,) into an actual number, the same thing could be achieved with *1 or any other operation that does not change the numerical value would also work. You may need to change the commas (,) for semicolons (;) on your machine

Promethee · Accepted Answer · 2025-06-23 19:48:15Z

0

Maybe this can be done with a simpler formula but this one works (it considers that each sub-version has max 3 digits and then build a number by concatenating each subnumber with three digits (through leading 0s if necessary):

=LET(MyReworkedVersions, MAP(A1#, LAMBDA(MyCell, IFERROR(NUMBERVALUE(TEXTJOIN("",FALSE, TEXT(TEXTSPLIT(MyCell, "."), "000"))), ""))),
          MyMaxReworkedVersions, BYROW(MyReworkedVersions, LAMBDA(MyRow, MAX(MyRow))),
          MyMaxVersions, IF(MyReworkedVersions = MyMaxReworkedVersions, A1#, ""),
          BYROW(MyMaxVersions, LAMBDA(MyRow, TEXTJOIN("", TRUE, MyRow))))

answered Jun 23 at 19:48

Promethee

1166 bronze badges

Collectives™ on Stack Overflow

Excel - Sorting textual versions using dynamic arrays

4 Answers 4

3 Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related