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!
In attached Excel file, 3-Lists comparison function implemented - but the calc is so slow that Excel becomes unresponsive. Have set the calculation to Manual.
Looking for better ideas for this function which is faster and practical.
Thanks in advance.
I guess you want to align same values in one row.
For example,All Feb need in row 3 in below picture instead of in line 2 and line 7(two Febs)
One possible way is transform to one dimension and group by values and value index as key.
If by sql with sqlite,may faster than Excel.
If by sql,
//select * from ListsComparision limit 20;
cli_one_dim~ListsComparision~0;
create temp table aa as
select 数量||row_number() over (partition by 属性,数量) key,属性,数量 from ListsComparisionunion ;
//select * from aa;
cli_create_two_dim~aa~属性~数量;
select * from aa_two_dim;
