Fold multiple array elements into one element


Is there a simple/straightforward way to “fold” multiple array elements into one element based on the value of the elements?

For example, given this array:

a = "item_1_green; item_1_blue; item_2_green; item_2_blue;item_2_red;item_3_green;item_4_blue;item_4_red"

where the element separator “;”.

I’d like a new array, b, that contains this:

b = "item_1_green,item_1_blue;item_2_green,item_2_blue,item_2_red;item_3_green;item_4_blue,item_4_red"

where the element separator is still “;”, but the former items are joined with a “,”.

IOW, I’d like to put all elements that have “1” in them into a single element with a new separator, “,”. Then add all elements with “2”; then “3”, etc.

I can probably “brute-force” it with a loop, but I’ve seen such elegant solutions from you guys, that I thought I’d ask here first. :smiley:


– Mark

I solved it using a loop … but, if you have a “snazzy” solution that uses arrayfilter() and/or arraymerge() and/or any other built-in functions, I’d love to see that! :slight_smile:


– Mark

I think a loop is the only way to do this … probably.

My money’s on Dave to do another brilliant one-liner.


Ok, not brilliant and not one line but it works on the data given (after all I’m not Dave):

a = "item_1_green;item_1_blue;item_2_green;item_2_blue;item_2_red;item_3_green;item_4_blue;item_4_red"
arrayfilter b, d, ";", "item_"+import()+tagdata(c,"item_"+str(val(import())),",item_"+str(val(import())+1),1)
d=d+tagdata(c+¶,"item_"+ arraylast(b,";"),¶,1)
message d```

Output is: "item_1_blue,item_1_green;item_2_blue,item_2_green,item_2_red;item_3_green;item_4_blue,item_4_red"
1 Like

I guess just to prove that almost anything is possible with Panorama here is the code above as one message statement. Taking the data above that is assigned to the variable a we can get the desired final data output using only this gigantic line of code:

message arrayfilter(arraydeduplicate(arrayfilter(a,";",{striptonum(import())}),";"),";",|||"item_"+import()+tagdata(replace(arraysort(a,";"),";",","),"item_"+str(val(import())),",item_"+str(val(import())+1),1)|||)+tagdata(replace(arraysort(a,";"),";",",")+¶,"item_"+ arraylast(arraydeduplicate(arrayfilter(a,";",{striptonum(import())}),";"),";"),¶,1)

Note that both versions of this code assume the original data has primary and secondary elements starting with “item_” followed by a numeric value and no other numbers in the strings. Here is how all it works. First we create a new array listing all the numbers from the data string and use arraydeduplicate( to give a sorted list of each unique number. Then we replace the “;” separators in the data with commas so it is now a simple comma separated array. We now sort the original array and rebuild it with arrayfilter( using some literal text and tagadata( so we can reassign the “;” separators between the various groups. Last we have to add the final group that the tagdata( missed with the arrayfilter( formula using another tagdata(. I have not covered every detail but this the basic scheme I used.

1 Like


This is great. Thank you for taking the time to help out here.

The only thing I’d change is the assignment of array “b” to be:


making the assumption that the 2nd item of the “_” delimited element contains the item number. That way, there can be numbers in other “sub-fields”.

But, my original request didn’t specify this. :smiley:


– Mark

Brilliant Gary, absolutely superb!


I thought I’d make a final point regarding this whole exercise. Although it is possible to consolidate a whole batch of code into a single compact line, it is not really beneficial in most cases. Unless you can achieve a substantial speed boost or you actually need a deeply nested line of code for a formula you are much better off keeping the various parts as separate lines - preferably with additional comments for clarification. I would hate to have to come back to the single line of code I entered above in 6 months or a year and try to figure out exactly what the heck I had done and why. I’m sad to say that I learned this lesson from experience and now try to consciously keep my code as simple and readable as possible. I don’t always succeed. :disappointed:

1 Like

Hey Gary,

You mean, you don’t write self-documenting code – like the amazing one-liner you put together? LOL!

What you say is very true. I have no problems favoring multiple, commented lines (which may run a few milliseconds slower) over cool, tricky, but not-as-self-evident one-liners.

Thanks for the reminder! :slight_smile:

– Mark