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!
Thanks m_tarler for the idea about holding pairs of candidate matched-indices as complex nos.
I studied (see attachment), and i expect that filtering-out the monotonically increasing nos. would still need recursion which i already implemented and due to it, as Mr. Peter pointed out, the order of complexity is expected to remain exponential. Thanks for your suggestions. When time permits, i may explore the approach further nonetheless.
So I worked your sheet a little further and you can tell me if the performance is still holding up. Basically once you find the list of 'imaginary numbers' I then do a complete list of all possibilities and then filter out the possibilities that are increasing and then pull the list with the most in the list
see attached
m_tarler, Thanks!
I also followed your suggested approach while you did and made a performance comparison.
There is significant improvement till no. of elements are small, but then it does increase exponentially.
e.g. a 3-List , 100-elements each comparison took 4.5 minutes to compute! (Excel could do the calc without becoming unresponsive forever!).
Have made comparison using stop-watch on my machine in updated attached Excel file.
The crux is, earlier i was comparing each of the element of ListA with each of the element of ListB (with some truncation),
Your suggestion about looking only within the candidate matches improved the performance significantly, still slow for longer lists, but perhaps about that far only we can go.
Thanks!