Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Mar 07, 2025
Solved

Filter and Sequence Function

Dear Experts,                   I have a Data like below:- The Column"D" can have the values from 1..5 but in Sequence only, I need to filter only the ones where this txNumber is starting from...
BI & Data Analysis
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Mar 07, 2025

    In that last column use the following and fill down:

    =IF((D2>1)+(D3>1),"grp-"&COUNTIF(D$1:D3,2)-1,"")

    I will try attaching again but sometimes this forum will filter files (especially .xlsm & .xlsb)

Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 07, 2025

I'm late to the show. Here's my approach with dynamic arrays:

=LET(
    a, Table1[hfnTickCount] =
        VSTACK(DROP(Table1[hfnTickCount], 1), 0),
    b, Table1[hfnTickCount] =
        VSTACK(0, DROP(Table1[hfnTickCount], -1)),
    c, Table1[dlFdSchedData.txNumber] <
        VSTACK(DROP(Table1[dlFdSchedData.txNumber], 1), 10),
    d, Table1[dlFdSchedData.txNumber] >
        VSTACK(10, DROP(Table1[dlFdSchedData.txNumber], -1)),
    FILTER(Table1, a + b + c + d >= 3, "None")
)

Notes on the solution included in the workbook.

Resources