0

In each cell of my column G, i have a list of elements like this :

['element1','element2','element3',...]

In some cells i have the same list, but not ordered in the same way:

For example, in G2 i have ['element1','element2','element3'] and in G250 i have ['element3','element1','element2'].

I did a script to order each list alphabetically (to compare if they are the same), but i want it to be automatic because the values come from an import range, and data can be added every day.

So i tried a formula : =trim(substitute(concatenate(transpose(sort(transpose(split(G2,"'"))))),",","")) that works for what i'm doing with it. The result looks like this : []element1element2element3.

Lists Ordered List with basic formula in each cell Ordered List with arrayformula
['element1','element2','element7','element8','element55'] []element1element2element55element7element8 #VALUE!
['element6','element5','element3','element2'] []element2element3element5element6
['element2','element3','element6','element5'] []element2element3element5element6
['element9','element7','element4','element88'] []element4element7element88element9
['element4','element88','element7','element9'] []element4element7element88element9
['element4','element8','element9'] []element4element8element9

But i cant use arrayformula =arrayformula(trim(substitute(concatenate(transpose(sort(transpose(split(G2:G,"'"))))),",","")))

I have an error

The textual result of CONCATENATE exceeds the allowed limit, which is 50000 characters.

After some research on internet, it seems that the error come from concatenate (i tried with join, and it doesnt work either).

There isn't 50k characters in my lists so i suppose that it's trying to concatenate all the cells of my array formula.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
ComeSFI
  • 23
  • 5
  • @Neo Consider asking for a markdown table instead. – TheMaster Oct 25 '22 at 09:50
  • 2
    Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 25 '22 at 09:50
  • The error says you're referencing empty cells. Avoid [traditional way of using array formula](https://stackoverflow.com/a/46884012/) – TheMaster Oct 25 '22 at 09:55
  • @TheMaster This is not a problem of empty cells. if I drag down my formula (not array fomula) it works, and if my arrayformula is like this : =arrayformula(trim(substitute(concatenate(transpose(sort(transpose(split(G2,"'"))))),",",""))) i have no error, but only one cell is filled – ComeSFI Oct 25 '22 at 10:14
  • Clear G2. You'll have the same error. – TheMaster Oct 25 '22 at 10:16
  • @TheMaster yes but my other cells are not empty, i'm trying to add a table to my question so you can see – ComeSFI Oct 25 '22 at 10:20
  • If you click on my link on avoiding traditional usage, you'll fix at least part of the problem: Referencing empty cells. – TheMaster Oct 25 '22 at 10:22
  • @TheMaster I can try, but i dont have any ampty cells in the G column, and besides, i dont understand how i should change my arrayformula to avoid traditional usage – ComeSFI Oct 25 '22 at 10:35
  • `i dont have any ampty cells in the G column` That's a lie! Are you saying there are no empty cells in column G from top to bottom of that column? – TheMaster Oct 25 '22 at 10:38
  • @TheMaster That's exactly what i'm saying – ComeSFI Oct 25 '22 at 10:40
  • `=COUNTBLANK(G2:G)` – TheMaster Oct 25 '22 at 10:44
  • @TheMaster the result is 0, but i probably had blank before because my error isn't the same anymore. Probably a change in the imported range. So i will have to modify my arrayformula to avoid this error in the future, but i didn't understand how with your link – ComeSFI Oct 25 '22 at 10:52

1 Answers1

5

SORT each row after SPLITting using BYROW and CONCATENATE:

=BYROW(G2:G7,LAMBDA(row,CONCATENATE(SORT(TRANSPOSE(SPLIT(row, "[',']"))))))
Lists Ordered List with arrayformula
['element1','element2','element7','element8','element55'] element1element2element55element7element8
['element6','element5','element3','element2'] element2element3element5element6
['element2','element3','element6','element5'] element2element3element5element6
['element9','element7','element4','element88'] element4element7element88element9
['element4','element88','element7','element9'] element4element7element88element9
['element4','element8','element9'] element4element8element9
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks for your time, it works perfectly and it's way easier than my solution. I had to edit the characters used for the split because i had some [] and , inside my elements name, but i did it in no time. Have a great day – ComeSFI Oct 25 '22 at 12:24