Given the condition that Y will appear as the 3rd character.

Try this:

[Filter]=if(Substr([Object];3;3)="Y") then "Hide" else "Show"

If Y can appear any where in the string and then you need to filter out the data. Try this:

[Filter]=if(Substr([Object];1;Length([Object]))="Y") then "Hide" else "Show"

Kuldeep, thanks. But the formula is giving me something different. Its recognising Y for all. I just need it to add Y to the corresponding Nos with Cr. Side. If there is no Y in debit side then l want to filter those out.

See what l have and result am looking to get below.

Nos. Signal

10. Y - Db
10. # - Cr
22. # - Db
22. # - Cr
24. Y - Db
24. # - Cr
35. # - Db
35. # - Cr
36. # - Db
36. # - Cr
37. Y - Db
37. # - Cr

This is what I want left

Nos. Signal

22. # - Db
22. # - Cr
35. # - Db
35. # - Cr
36. # - Db

Hi,

Can you try this by tweaking formula provided by Kuldeep:

=If ( ([Nos] = Previous([Nos]) and (Substr([Signal];1;Length(Signal))="#")) then

[Signal]

Thanks,

Jothi

Jothi its off. I used your formula as below too:

= (([Nos] = Previous([Nos]) and(Substr([Signal];1;Length(Signal))="#"))

I have thousands of rows. Most rows with Y under Signal Column but their corresponding accounting entry is # under same Signal Column. I need a formula to help me filter both Y and # row with same No.

So for every Y with a #, they have same No which can be used to identify them. Help!! Please

See my 2 columns below. The Db and Cr is just to show more info.

Nos. Signal

10. Y - Db
10. # - Cr
22. # - Db
22. # - Cr
24. Y - Db
24. # - Cr
35. # - Db
35. # - Cr
36. # - Db
36. # - Cr
37. Y - Db
37. # - Cr

This is what I want left

Nos. Signal

22. # - Db
22. # - Cr
35. # - Db
35. # - Cr
36. # - Db

36. # - Cr

Follow these steps.

Create V Nos=Substr([Nos];1;2) (This will give you the first two values like 10,22 etc)

Var1=If(Match([Nos];"*Y*")) In ([V Nos]) Then "Hide" Else "Show"

Var2=If(IsError([Var1])) Then "Hide" Else "Show"

Apply filter on Var2 where equal to Show and see.

Only the Y has "Show' using your variable on my end, all other rows with # has "Hide so unable to take corresonding entries out.

1. Can u help me understand why 3 variables?

Also using your variables all rows with Y come up with "hide "while other # has Show and when l try to apply filter l only see Show as the only value from list to filter from even though l could see Hide and Show in report body.

Not sure how you get this on your end.

I really need help with this. Thanks

Things to note on my end Amit. One column is Nos like below which may not necessarily be 2 digit some 3 or more. Another column have the dr and cr indicator which help tie them out to one Nos. Sample columns below:

Nos

10
10
22
22
24
24
35
35
36
36
37
37

Signal Column: I used concatenate funtion to tie the Y or # to Nos

Signal

10Y
10#
22#
22#
24#
24#
35#
35#
36#
36#
37Y
37Y

Then l have another column for dr and cr indicator. Note: for every debit there is credit side, however there could be more than 2 no for each dr and cr side e.g

10 Cr

10 Cr

10 Cr

10 Db

10 Db

The only issue with the above is that for each Nos like 10, there may just be one Y signal while others may be # but the Nos help me tie each group as one so l can then filter out all Nos like 10 with one or 2 Y where they have other #. I will share screenshot shortly.

Lastly l just used your variable below - tried the steps too though

Var1=If(Match([Signal];"*Y*")) In ([Nos]) Then "Hide" Else "Show"

With above, I get Hide where Y only and not corresponding # with same No, l didnt get multivalue error and surprise l only see Hide as the only option from filter section so l just used Not In List of Hide. This works but l need the # with Y nos out too.

Thanks so much for assisting. Appreciate thus greatly.

Hello Amit - See attached.

One shows the Report while the other shows the Filter page.

I used your formula steps and see what I get in Screen attched

From attached, There's No, Nos, Signal, V.2, and V.3 Fields. What I want left from the Nos Field is row with 350007474 - which also comes up "Hide" under V.3

Note:

1. Some of my Nos do not have same digits (they could be one, two or more less than others)
2. I concatenate Nos with Key to get Signal and No with another Field to get Nos - Screen shot should help explain what I mean here
3. Filter Page only shows Hide option and not Show too - any reason why? But when I used not in List of Hide it does recognize it though

Thanks for your help on this.

sap.png

sap1.png

Thank You So much Amit. Works! Works!! Works!!!.