Forum Discussion
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 1 but ending >1
So, all the entries where we have the seq- 1..2, 1..3,1..4,1...5, but not 1...1 , that means no Retransmission
txNumber=1 is always a New-Tx,
now I did it manually one by one.. but eats a lot of time
How to do such grouping by a Formula.
Thanks in Advance,
Br,
Anupam
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)
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.
Try this in E2 and copy down:
=IF(AND(D2<>1,D2=N(D1)+1),"Re-Tx","New-TX")
Thanks Riny_van_Eekelen , not like that.. let me elaborate more, So in last column create a grouping logic like,
the 1st sequence (from 1... any num >1) = grp-0, and another such set = grp-1 and so on.
Br,
Anupam
one option is to use conditional formatting on that column with a formula like:
=(D1>1)+(D2>1)
and choose a unique fill color
and then filter by that color
(see attached)
alternatively you could do the same thing using a helper column and then filter by that column
(in the attached I filtered out the zero values)
Thanks m_tarler with your logic yes atleast it colors the required date into a specific color so can be grouped, can we modify some more logic so that in the last column with the criteria you mentioned it will group them into grps 0,1,2,?
Thanks,
Br,
Anupam
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)
