formula

Christine Menke

Hi Experts,

I have the following table and I would like to show in column "Rounds" a number. How many rounds we have ordered the article for this stage.

I'm looking for a formula which checks the same article e.g. AA with the same stage e.g. CS1 and than return 1 with the lowest date, 2 for .... and 3 for the latest date. So I would like to bring them in an order.

The result should be :

row 1 = round 1. row 4 = round 2.

Thanks

Genevieve P.

Hi @Christine Menke

How are the rows being created in the sheet? For example, are you using a Form so the most recent/max dates will always be inserted at the top of the sheet?

If the sheet will always be Sorted with the Max date up at the top of the sheet, we could use an Auto-Number column to number the rows. Then we can use this number column to RANK the rows (using the RANKEQ Function) based on your criteria and return what number Round that row is associated with.

What I might suggest doing is have an extra helper column in the sheet that looks at the auto-number column and returns the row number instead of the auto-number. The reason I'd do this is so that you can add rows and then use the SORT function to ensure that the max date is at the top of the sheet, so your formula works even if the order of when the rows were created is jumbled.

Ex:

=MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)

Even though my Auto-Number has my newest row at the top, I can use this Row Number formula to generate the row number for my RANK formula:

="Round " + RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], Article:Article, Article@row, Stage:Stage, Stage@row), 0)

Let me know if this makes sense and will work for you!

Cheers,

Genevieve