Home > Mobile >  Create dynamic array from non-contiguous cells/values without VBA
Create dynamic array from non-contiguous cells/values without VBA

Time:09-27

NB This question refers to some features like optional LAMBDA arguments and the ISOMITTED function which are only available (at the moment) in the Beta channel (more info demo of calling ARRAYFROMARGS Although this can only accept values which can be coerced into Variants so not lambdas or linked data types for example.

CodePudding user response:

Well as I alluded to in the question, I've found a way to use a binary search to evaluate how many arguments are missing. It does involve a lot of copy-paste though. So here's code to evaluate the number of args passed by finding the first which is missing:

Tag Value
Name ARGSCOUNT
Scope Workbook
Comment Use a hardcoded binary search to find the first omitted argument, in chunks of up to 63
Refers To =LAMBDA([p_1],[p_2],[p_3],[p_4],[p_5],[p_6],[p_7],[p_8],[p_9],[p_10],[p_11],[p_12],[p_13],[p_14],[p_15],[p_16],[p_17],[p_18],[p_19],[p_20],[p_21],[p_22],[p_23],[p_24],[p_25],[p_26],[p_27],[p_28],[p_29],[p_30],[p_31],[p_32],[p_33],[p_34],[p_35],[p_36],[p_37],[p_38],[p_39],[p_40],[p_41],[p_42],[p_43],[p_44],[p_45],[p_46],[p_47],[p_48],[p_49],[p_50],[p_51],[p_52],[p_53],[p_54],[p_55],[p_56],[p_57],[p_58],[p_59],[p_60],[p_61],[p_62],[p_63],IF(ISOMITTED(p_32),IF(ISOMITTED(p_16),IF(ISOMITTED(p_8),IF(ISOMITTED(p_4),IF(ISOMITTED(p_2),IF(ISOMITTED(p_1),0,1),IF(ISOMITTED(p_3),2,3)),IF(ISOMITTED(p_6),IF(ISOMITTED(p_5),4,5),IF(ISOMITTED(p_7),6,7))),IF(ISOMITTED(p_12),IF(ISOMITTED(p_10),IF(ISOMITTED(p_9),8,9),IF(ISOMITTED(p_11),10,11)),IF(ISOMITTED(p_14),IF(ISOMITTED(p_13),12,13),IF(ISOMITTED(p_15),14,15)))),IF(ISOMITTED(p_24),IF(ISOMITTED(p_20),IF(ISOMITTED(p_18),IF(ISOMITTED(p_17),16,17),IF(ISOMITTED(p_19),18,19)),IF(ISOMITTED(p_22),IF(ISOMITTED(p_21),20,21),IF(ISOMITTED(p_23),22,23))),IF(ISOMITTED(p_28),IF(ISOMITTED(p_26),IF(ISOMITTED(p_25),24,25),IF(ISOMITTED(p_27),26,27)),IF(ISOMITTED(p_30),IF(ISOMITTED(p_29),28,29),IF(ISOMITTED(p_31),30,31))))),IF(ISOMITTED(p_48),IF(ISOMITTED(p_40),IF(ISOMITTED(p_36),IF(ISOMITTED(p_34),IF(ISOMITTED(p_33),32,33),IF(ISOMITTED(p_35),34,35)),IF(ISOMITTED(p_38),IF(ISOMITTED(p_37),36,37),IF(ISOMITTED(p_39),38,39))),IF(ISOMITTED(p_44),IF(ISOMITTED(p_42),IF(ISOMITTED(p_41),40,41),IF(ISOMITTED(p_43),42,43)),IF(ISOMITTED(p_46),IF(ISOMITTED(p_45),44,45),IF(ISOMITTED(p_47),46,47)))),IF(ISOMITTED(p_56),IF(ISOMITTED(p_52),IF(ISOMITTED(p_50),IF(ISOMITTED(p_49),48,49),IF(ISOMITTED(p_51),50,51)),IF(ISOMITTED(p_54),IF(ISOMITTED(p_53),52,53),IF(ISOMITTED(p_55),54,55))),IF(ISOMITTED(p_60),IF(ISOMITTED(p_58),IF(ISOMITTED(p_57),56,57),IF(ISOMITTED(p_59),58,59)),IF(ISOMITTED(p_62),IF(ISOMITTED(p_61),60,61),IF(ISOMITTED(p_63),62,63)))))))

It is called like this =ARGSCOUNT(arg_1, arg_2, ..., arg_63) from an enclosing LAMBDA. Note it takes up to 63 optional arguments because my binary tree is symmetrical so must be a power of 2 (minus 1 for the case of 0 arguments) and named references have a low character limit around 2000. However you can call it more than once from a parent function and sum the result e.g. ARGSCOUNT(arg_1, ..., arg_63) ARGSCOUNT(arg_64, ..., arg_126)

Then that count can be used in a second LAMBDA function to build the array:

Tag Value
Name ARRAY
Scope Workbook
Comment Create an array from comma separated arguments, up to 130
Refers To =LAMBDA(_0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],[_119],[_120],[_121],[_122],[_123],[_124],[_125],[_126],[_127],[_128],[_129],CHOOSE(SEQUENCE(ARGSCOUNT(_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63) ARGSCOUNT(_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126) ARGSCOUNT(_127,_128,_129) 1),_0,_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63,_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126,_127,_128,_129))

Easier to copy versions ARRAY function


BONUS:

You can even create an array of LAMBDAS to pass to something like map:

=MAP(ARRAY(LAMBDA(x, x^2), LAMBDA(y, y 1)),LAMBDA(f, f(3)) // -> {9,4} i.e. 3^2, 3 1

... and the vba ARRAYFROMARGS func can't take LAMBDAS as arguments.

CodePudding user response:

If Array1 is defined as:

=CHOOSE(SEQUENCE(number_of_args), arg_1, arg_2, arg_3, ..., arg_n))

where number_of_args is such that number_of_args >= n, then your desired result is derived via:

=INDEX(Array1,SEQUENCE(SUM(1-ISERR(Arry1))))
  • Related