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 -> I need the start of the hfn/sfn/slot and in J/K/L the end of the hfn/sfn/slot for that File Name as populated , How to achieve this?Thanks & RegardsAnupam Shrivastava

Filter Function or TAKE-DROP Function | Microsoft Community Hub

=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)), ", ", ";")) )

8 Replies

Patrick2788
Silver Contributor
Mar 06, 2026
You can solve this on streak detection alone. Streak detection in this sense requires your data to be sorted and it looks like your sample is sorted.
=LET( start, FILTER(HSTACK(FileName, Details), Streakλ(FileName) = 1), end, FILTER(Details, Countdownλ(FileName) = 1), final, HSTACK(start, end), final )

Streakλ and Countdownλ being two functions I use for situations like this.
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

IlirU

Iron Contributor

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

m_tarler
Bronze Contributor
Mar 05, 2026
alternatively:
=LET(in,A2:.D1000, fnames,UNIQUE(TAKE(in,,1)), fn_out,LAMBDA(fn,LET(a,FILTER(in,TAKE(in,,1)=fn),HSTACK(TAKE(a,1),DROP(TAKE(a,-1),,1)))), DROP(REDUCE("",fnames,LAMBDA(p,q,VSTACK(p,fn_out(q)))),1))
If only I could get a better grip on Thunks so I could get away from the DROP(REDUCE()) work around for 2d arrays.
another alternative inspired by IlirU use of GROUPBY:
=LET(fnames,A2:.A999,data,B2:.D999, DROP(HSTACK( GROUPBY(fnames,data,HSTACK(MIN,MIN,MIN),,0), DROP(GROUPBY(fnames,data,HSTACK(MAX,MAX,MAX),,0),,1)),1))
- anupambit1797
  Steel Contributor
  Mar 09, 2026
  m_tarler for the alternative which you proposed for the Groupby function could you please attach the spreadsheet using the formula.. I am getting something like this in my worksheet.

Forum Discussion

Filter Function or TAKE-DROP Function