Remove Cancelled Transactions from FILTER Output

[Mgc26133]

Hello all!

I have a data I'm filtering a certain report from using FILTER. Problem that I discovered today, is that there is a criteria missing which I can't seem to figure out how to formulize.

I have explained further in the attached file, since it will be confusing to explain it here without context.


All help is appreciated!

Best regards

[Fluff13]

How about

Please Login or Register  to view this content.

[Mgc26133]

Hi Fluff

I see what you're going for. However slight issue .. I prepared this sample sheet for simplicity, but the original data contains thousands of plate numbers, each have their own thousands of passes; so among them, expectedly, there are many toll usages from same gates with the same prices (irrespective of dates & plate numbers). So this carries the risk of your formula to exclude a lot of passes that aren't necessarily cancelled.

For example if you copy A17:J17 to A18:J18 (making so there are 2 B's but 1 A), you will see the formula is not including the 2nd B, which is not a cancelled pass.

Can you think of a workaround?

[Fluff13]

Whilst we can use the date & car to help endure that wrong values are not removed. I'm not sure how to account for the scenario you get by copying row 17 down

[Fluff13]

If the cancelled rows are always below the row with an A you could use

Please Login or Register  to view this content.