Forum Discussion
Conditional Formatting or a Specific Filter Rule
Dear Experts ,
I have a data like below:-
here the Column-O, txNumber can go from 1,2,3 (txNum=2 and 3 means a Retransmission), I want to filter all the pairs for all Transmissions and their corresponding Retransmissions ( either by coloring them all using a conditional formatting) or using a specific filter Rule.
How to identify the Transmission and it's corresponding Retransmission for a Specific HarqId( say for dlHarqProcessIndex ==7, the blue color
Row-37 is my 1st transmission(txNumber ==1) for the dlHarqProcessIndex==7 with a tbSize of 852696, and adaptRetxStatus == NOTACTIVATE, and it's corresponding 2nd transmission( Retransmission , txNumber==2) Row-45, tbSize remains same(as it's corresponding 1st transmission) in all retransmissions and adaptRetxStatus changes to "APPLY" for all retrans txNumber==2,3) and similarly for other pairs like for dlHarqProcessIndex==13.
Attached is the Worksheet.
Oliver's Power Query is really nice but I think it is missing the txNumber=3 lines (I'm betting he can tweak it to include them)
Here is a formula I used as a helper column:
=IF([@txNumber]>1, [@tbSize], IFERROR(LET( r,ROWS(Tabelle1[[#Headers],[tbSize]]:[@tbSize])-1, PRODUCT(TAKE(FILTER( HSTACK(DROP([tbSize],r),DROP([txNumber],r)-1), [@dlHarqProcessIndex]=DROP([dlHarqProcessIndex],r), {0,0}),1))),0))the output is 0 if it isn't one of the resent lines or the tbSize value if it was (replacing this with just TRUE/1 would make the formula a little more simple but wanted it as a double check)
as for my method (which may be flawed) was to include all lines that have a txNumber>1 or if it is 1 then look for the NEXT line that has the same HarqID and if that line has a txNumber>1 then include it (i.e. next time that HarqID is used was a re-transmit).
I will try to attach the file separately
5 Replies
An alternative could be Power Query if i understand what you want to achieve. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. I've only copied and pasted columns A to T from your sample file.
Oliver's Power Query is really nice but I think it is missing the txNumber=3 lines (I'm betting he can tweak it to include them)
Here is a formula I used as a helper column:
=IF([@txNumber]>1, [@tbSize], IFERROR(LET( r,ROWS(Tabelle1[[#Headers],[tbSize]]:[@tbSize])-1, PRODUCT(TAKE(FILTER( HSTACK(DROP([tbSize],r),DROP([txNumber],r)-1), [@dlHarqProcessIndex]=DROP([dlHarqProcessIndex],r), {0,0}),1))),0))the output is 0 if it isn't one of the resent lines or the tbSize value if it was (replacing this with just TRUE/1 would make the formula a little more simple but wanted it as a double check)
as for my method (which may be flawed) was to include all lines that have a txNumber>1 or if it is 1 then look for the NEXT line that has the same HarqID and if that line has a txNumber>1 then include it (i.e. next time that HarqID is used was a re-transmit).
I will try to attach the file separately
here is Oliver's file updated with my helper column
Attaching zip file
Attachment
