Forum Discussion
Lists Comparison (Stacked & Keeping order)
Interesting project. I see you've found a solution but indicate there are still performance issues with larger amounts of data. One thing to keep in mind is that iterative functions like MAKEARRAY, MAP, SCAN and REDUCE will perform very poorly when using INDEX over an array object (as opposed to a physical range). This may be a contributing factor since a number of your custom functions use iterative indexing methods (not to mention some iterative stacking as well).
In the attached file I've used an alternative approach for generating the list of matching indices and row counts, then used SORTBY with EXPAND and TOCOL-IFS-SEQUENCE to pad each array with the appropriate number of blank rows. It seems to be generating the same results as your original formulas, plus it can handle 5 separate lists with 2000 rows each in < 1 second. I've put the new lambda definitions on a separate worksheet, for your convenience. Check it out and see if it's working as expected. Cheers!
Interesting project. I see you've found a solution but indicate there are still performance issues with larger amounts of data. One thing to keep in mind is that iterative functions like MAKEARRAY, MAP, SCAN and REDUCE will perform very poorly when using INDEX over an array object (as opposed to a physical range). This may be a contributing factor since a number of your custom functions use iterative indexing methods (not to mention some iterative stacking as well).
In the attached file I've used an alternative approach for generating the list of matching indices and row counts, then used SORTBY with EXPAND and TOCOL-IFS-SEQUENCE to pad each array with the appropriate number of blank rows. It seems to be generating the same results as your original formulas, plus it can handle 5 separate lists with 2000 rows each in < 1 second. I've put the new lambda definitions on a separate worksheet, for your convenience. Check it out and see if it's working as expected. Cheers!
Thanks, it is tremendously fast! Indeed, it takes <1 second even for large lists.
Change of the basic approach was what I was looking for and needed.
I needed List comparisons as a sub-tool (part) of a bigger subsequent calc and now it is efficient and helpful.
I tallied the results as well as timed the calculation: -
2-Lists, 1000-Elements each
(1) Iterative (Makearray/Map/Vstack/Index/Thunks) : 3 minutes
(2) Direct (Sortby/Expand/Tocol/Ifs/Sequence) : 50 milliseconds
3-Lists, 1000-Elements each
(1) Iterative (Makearray/Map/Vstack/Index/Thunks) : 10 minutes
(2) Direct (Sortby/Expand/Tocol/Ifs/Sequence) : 130 milliseconds
The time taken figures mentioned above are not exactly repeatable on re-calc, but the order remains more or less the same. Refer attachment for comparison of different approaches.
Thanks!
You're welcome! I'm glad it worked out for you.
BTW, I'm guessing the use of double-quotes ("""") as opposed to zero-length strings ("") in your attached file was intentional??? If not, simply adjust the [pad_with] argument of each of the 4 instances of EXPAND in your BySortbyExpandTocolAndIfsEtc.ListCompλ function definition from """" to "" in order to get rid of them.
Cheers! :)