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!
PeterBartholomew1, thanks but it won't be practically possible to add date structures manually.
In fact, the implementation is required to be done on text arrays which could run upto about 500 elements long. Jan,Feb etc. i added as example texts only.
In attachment, I have included a more relatable example of persons being members of different teams, and teams being compared without disturbing the original order which may represent seniority of the members. Other use case could be the code comparison for which there are online tools available (example links included in attached Excel), but I need the similar implementation in Excel.
Going by the first principles method by use of indices of matched elements and working around them, I could do it, but this becomes too slow in Dynamic Arrays. (I'm wondering if implemented by VBA would it be faster?)
PIVOTBY is a nice trick which I take note as of help elsewhere but for present problem, making that helper structure in-between defeats the purpose of automating the comparison.
Re:won't be practically possible to add date structures manually.
VBA may an option to automate it.
But I would prefer sql which looks more consice and running faster.