Limiting column values at the maximum value

Hello,

I have a project data and I am calculating the number of projects reaching a certain gate in a certain month. I would like to limit the data that if date is the maximum and value is the maximum per project then display max date and max value per project only once. So the rest of the fields after latest date with first max value would be removed or replaced with null. In the example, Project one would have only data until 201901 and 201902 would be removed because it is the latest date with first max value per project. How could I do it?

-Anastasiia

Output

Run Command

Accepted answers

brindhan

Hi @Anastasiia

Hope this is the result you need. Enjoy!

Cheers,

solution.yxmd

All comments

BenMoss

Hi!

I'm struggling to understand completely what you are looking for, so could you please post what your output data should look like.

Also, it would be great if you could provide the initial data as an excel file, just so people don't have to spend time unnecessarily replicating it.

Ben

brindhan

Hi @Anastasiia

Hope this is the result you need. Enjoy!

Cheers,

solution.yxmd

Anastasiia

@brindhan, hi!

Thank for the solution. Just what I needed. Multi-row formula is a new tool for me. Thanks for the help!

Anastasiia

@BenMoss, hi!

I was looking for a way how to limit number of rows when a project has the max value in Value column and have the latest date. In my example, I have Project 1 and two rows: 201901 Value 12 and 201902 Value 12. So I wanted to display only 201901 Value 12 and remove the dates after 201901 with max value (in this case, 201902 Value 12)

Multi-row formula presented in below solution helped to achieve result.

I have already received the solution. Thank you!

-Anastasiia

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703