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!
There are approaches that would expand to 3 or more columns provided the 'months' can be interpreted as dates with a natural sort order (otherwise I suspect the results are ambiguous). For example
corresponds to your initial problem but starting with Excel dates rather than the abbreviated names of months. The intermediate step is a combined list of tagged dates.
= LET(
taggedA, EXPAND(listA,,2,"List A"),
taggedB, EXPAND(listB,,2,"List B"),
combined, SORT(VSTACK(taggedA, taggedB)),
group, TAKE(combined,,-1),
month, TAKE(combined,,1),
DROP(PIVOTBY(month, group, month, MAX,,0,,0),,1)
)This is fast, but it required additional structure within your data.