# Rank in a group with IF conditions - level: extreme

Hello

i would like to ask if someone can solve my “puzzle”.

I need to rank (1 or 2) some contracts per customer by importance under certain conditions. For example Customer A has 3 Contracts and i need to rank them only one by 1 and the other two by 2.

I write the conditions, i tried with groupby and ranking but i don t know how to insert IF conditions not only for some values in cells but also for the number of rows

The conditions are:

-hyerarchy of column “Category” (i ranked by one my custom tabel)

• number of rows
• area in m^2

It is possible that the contracts may be in other streets. For example i have 2 contracts for via Pippo 1 and 2 and another 2 contracts for via missoni 1. In this case i have to put two “1” (one for via pippo 1 and the other one for via missoni 1) and two 2.

I don t have problem to calculate the ranking conditions (number of rows i used grupby, hyerarchy of “category” i use rank node), i have problem to use IF with area (IF this area is bigger than the other, wins) and i have a problem to concatenate all of this conditions to determine which contracts is 1 or 2.

I hope to be clear, ask if need (i know it s very difficult). in attach there s an example

byez
example.xlsx (10.5 KB)

Hi,
As I understand it your example only contains the input data. Could you also share the expected output? That would help to understand the problem better.
Kind regards,
Alexander

example_REV.xlsx (10.9 KB)

here it goes

thanks so much

the conditions are:

same id customer with different id contracts:

same street-> same category → area in m^2 WINS (so the id contract with the highest m^2 is 1 even if 2 has more rows)

same street → different category → this is the case where i ranked in a table the order of priority to make win one category on another and to decide if the ID contract is 1 or 2

same street → same category → area in m^2 is the same → id contract with more rows WINS (so it will be 1 and the other/s 2)

i know it s complicated but i can t make a concatenate of IF with the cases above