Filter Function or TAKE-DROP Function

Dear Experts, I have a Data like this:- Column A -> Has the File Names, and Column B,C,D have their corresponding data, In Column F I have the unique File Names and from G/H/I...

BI & Data Analysis

excel

Formulas and Functions

power bi

IlirU

Mar 05, 2026

anupambit1797,

Try below formula in cell F2:

=LET(
     colA, A2:.A100,
       tj, TEXTJOIN,
       ts, TEXTSPLIT,
      arr, ARRAYTOTEXT,
    HSTACK(UNIQUE(colA), --ts(tj(";",, BYROW(WRAPROWS(BYROW(TRANSPOSE(ts(tj(";",,
     BYCOL(B2:.D100, LAMBDA(b, arr(INDEX(b, DROP(DROP(GROUPBY(colA, SEQUENCE(ROWS(colA)),
    HSTACK(MIN, MAX),, 0), 1),, 1)))))), ", ", ";")), arr), 2), arr)), ", ", ";"))
)

Hope this helps.

IlirU

SergeiBaklan

Diamond Contributor

Mar 06, 2026

As variant

=LET(
  names, UNIQUE(filename),
  CHOOSECOLS(
     HSTACK(
       names,
       XLOOKUP(names, filename, TickCount,,,{1,-1}),
       XLOOKUP(names, filename, sfn,,,{1,-1}),
       XLOOKUP(names, filename, slot,,,{1,-1})
     ),
  {1,2,4,6,3,5,7}
  )
)

anupambit1797
Steel Contributor
Mar 09, 2026
Hi SergeiBaklan can you please share your formula with the attached worksheet I provided.. seems I am missing something
Br,
Anupam

Forum Discussion

Filter Function or TAKE-DROP Function