Remove all rows if one occurence happens

[daniel1409z]

I have a table like this one

ID Letter
1   A
1   A
1   B
2   A
2   A
2   A
3   A
3   B
3   B

What I need to do is remove all rows from the IDs if only one of the lines of that ID contains the value B.
In this exemple, the result should be

2   A
2   A
2   A

How exactly can I do this? Thanks in advance

[MFGF]

I have a table like this one

ID Letter
1   A
1   A
1   B
2   A
2   A
2   A
3   A
3   B
3   B

What I need to do is remove all rows from the IDs if only one of the lines of that ID contains the value B.
In this exemple, the result should be

2   A
2   A
2   A

How exactly can I do this? Thanks in advance

Hi,

I'm not quite clear on the exact requirement - the description you wrote doesn't seem to match the data you posted?

ID 1 has one row containing B - so that should be removed
ID 2 has no rows containing B, so that should be displayed
ID 3 has two rows containing B - should it be removed or displayed?

Your description said remove all rows from the IDs if only one of the lines of that ID contains the value B, so if that's true, ID 3 should be displayed? In the results table it isn't though?

If ID 3 should be removed, then a simple filter maximum([Letter] for [ID]) <> 'B' should do the trick

If ID 3 should be displayed, then the filter should be maximum([Letter] for [ID]) <> 'B' or (maximum([Letter] for [ID]) = 'B' and count([Letter] for [ID]) > 1)

Cheers!

MF.

[daniel1409z]

Hi, thanks for the answer!

Yep, it was not clear indeed. In this case, I want to remove the ID 3 as well, sorry!


"If ID 3 should be removed, then a simple filter [Letter] <> 'B' should do the trick"

Correct me if I'm wrong, but if I include this filter, the IDs 1 and 3 will stay on the report, right? It will remove only the rows with the B but the rows with A will stay.

[MFGF]

Hi, thanks for the answer!

Yep, it was not clear indeed. In this case, I want to remove the ID 3 as well, sorry!


"If ID 3 should be removed, then a simple filter [Letter] <> 'B' should do the trick"

Correct me if I'm wrong, but if I include this filter, the IDs 1 and 3 will stay on the report, right? It will remove only the rows with the B but the rows with A will stay.

Yes - apologies. I realized my mistake immediately after posting and amended the post. It now reads:

If ID 3 should be displayed, then the filter should be maximum([Letter] for [ID]) <> 'B' or (maximum([Letter] for [ID]) = 'B' and count([Letter] for [ID]) > 1)

Does that work?

Cheers!

MF.

[daniel1409z]

Yep, the maximum filter worked. Thank you so much!!!