Please help: ranking column A duplicates based on unique value in column B

[Jakub_Martisek]

Hi there guys,

I am stuck with making the right formula for my situation. Let’s make though experiment that I have 1 TABLE that contains rows with 2 COLUMNS (or fields for every row).

COLUMN A

• includes numbers that can have duplicates for example: 1, 2, 3, 3, 4, 7, 7

COLUMN B

• has unique ID (also number) for example: 1, 2, 3, 4, 5, 6, 7

So the table would looks like like this:
1:1
2:2
3:3
3:4
4:5
7:6
7:7

And I need to make COLUMN C with formula that will rank COLUMN A by those conditions:

1. Firstly, rank will be decided by number in this column A only (1 will be 1, 7 will be 7, but as you can see you can get two 7, or two 3),
2. Then, duplicates will be decided by unique ID in COLUMN B.

So the result might look like this: (COLUMN A : COLUMN B: COLUMN C - RANK)
1:1:1
2:2:2
3:3:3
3:4:4
4:5:5
7:6:6
7:7:7

so the third number is actual rank.

HOPE, I EXPLAIN IT CORRECTLY, I AM NOT NATIVE ENGLISH SPEAKER.

If someone can help, this will save my life

Thank you

[Sebastien]

Just a quick idea:
if column B take values only from 0 to 999,999 (for example),
simply set up a formula in column C = 1,000,000x[thisrow].A + [thisrow].B

You can then sort your table by column C.

That doesn’t give you a rank as a succession of values from 1 to N, but if you need this information only within Coda, a rank is not needed (or a good idea). If you really need a succession of values, you can probably generate it with a button, with the mytable.sort([C]).ForEach(…)

[Sebastien]

Update: if you need a rank in column D, create a button with /button and the following formula for the OnClick field:

Table.ForEach(ModifyRows(CurrentValue,D,rank(CurrentValue.C,Table.C,true)))

[Sebastien]

Alternatively, if you’re adding rows to the table and want to avoid pressing that button on and on, set up column D with the following formula:

rank(thisRow.C,thisTable.C,true)

Welcome to this community @Jakub_Martisek !