XLS365 LAMBDA

01

ft.SORT_COLS = LAMBDA(table,
    LET(
        header, CHOOSEROWS(table, 1),
        values, DROP(table, 1),
        ncols, COLUMNS(table),
        headstack, VSTACK(header, SEQUENCE(1, ncols)),
        headsorted, SORT(headstack, 1, 1, TRUE),
        newindex, CHOOSEROWS(headsorted, 2),
        sortedvalues, CHOOSECOLS(values, newindex),
        sortedheader, CHOOSEROWS(headsorted, 1),
        VSTACK(sortedheader, sortedvalues)
    )
);

ft.FIND_INDEX2D = LAMBDA(lookup_value, table,
    LET(
        xindex, FILTER(
            SEQUENCE(ROWS(table)),
            BYROW(table, LAMBDA(array, OR(array = lookup_value)))
        ),
        yindex, FILTER(
            SEQUENCE(1, COLUMNS(table)),
            BYCOL(table, LAMBDA(array, OR(array = lookup_value)))
        ),
        HSTACK(xindex, yindex)
    )
);

ft.VSTACK = LAMBDA(array_input, ignore_blank, IF(ISOMITTED(ignore_blank),
  TEXTSPLIT(TEXTJOIN("xdelim",FALSE,TRANSPOSE(array_input)),,"xdelim",FALSE),
  TEXTSPLIT(TEXTJOIN("xdelim",TRUE,TRANSPOSE(array_input)),,"xdelim",FALSE)
));

ft.VSTACKxy = LAMBDA(array_input,
  LET(
    banyak_baris, rows(array_input),
    banyak_kolom, columns(array_input),
    olah1, TEXTJOIN("xdelim",FALSE,SEQUENCE(banyak_baris,1,1,1)),
    olah2, REPT(olah1&"xdelim",banyak_kolom),
    olah3, TEXTSPLIT(olah2,,"xdelim",TRUE),
    olah4, REPT(SEQUENCE(banyak_kolom,1,1,1)&"xdelim",banyak_baris),
    olah5, TEXTJOIN("xdelim",FALSE,olah4),
    olah6, TEXTSPLIT(olah5,,"xdelim",TRUE),
    hstack(olah3,olah6)
  )
);

ft.TOCOL2 = LAMBDA(array_input,
LET(
   pembagi_kolom, 2,
   banyak_kolom, columns(array_input),
   data1, TOCOL(TRANSPOSE(CHOOSECOLS(array_input,SEQUENCE(,banyak_kolom/pembagi_kolom,1,pembagi_kolom))),1),
   data2, TOCOL(TRANSPOSE(CHOOSECOLS(array_input,SEQUENCE(,banyak_kolom/pembagi_kolom,2,pembagi_kolom))),1),
   hasil, hstack(data1,data2),
   hasil
   )
  );

ft.TOCOL3 = LAMBDA(array_input,
LET(
   pembagi_kolom, 3,
   banyak_kolom, columns(array_input),
   data1, TOCOL(TRANSPOSE(CHOOSECOLS(array_input,SEQUENCE(,banyak_kolom/pembagi_kolom,1,pembagi_kolom))),1),
   data2, TOCOL(TRANSPOSE(CHOOSECOLS(array_input,SEQUENCE(,banyak_kolom/pembagi_kolom,2,pembagi_kolom))),1),
   data3, TOCOL(TRANSPOSE(CHOOSECOLS(array_input,SEQUENCE(,banyak_kolom/pembagi_kolom,3,pembagi_kolom))),1),
   hasil, hstack(data1,data2,data3),
   hasil
   )
  );

/* https://www.mrexcel.com/board/threads/repeatbynumber.1216836/ */
ft.RPTBYNR = LAMBDA(a,
  LET(
    b, TAKE(a, , -1),
    XLOOKUP(SEQUENCE(SUM(b)), SCAN(0, b, LAMBDA(v, i, v + i)), TAKE(a, , 1), , 1)
  )
);

ft.RPTNTIMES = LAMBDA(a,
  LET(
    b, TAKE(a, , -1), TOCOL(IF(b >= SEQUENCE(, MAX(b)), TAKE(a, , 1), NA()), 2)
  )
);

ini udah obsolete, bisa merujuk ke lambda.feid