Looking to get the first N elements from different elements of data

Attached is sample data to illustrate my issue with both sample data and solution.

I am looking to get the highest 3 from "A group", 3 from "B group", 2 from "C group" (blue in solution) and then the top 2 from any group that is left over (green in solution).

This needs to be identified (flagged) by year (2 groups of these top N amounts) and also by both quantity and amount separately. See solution tab for clarity.

Thanks in advance as I have been struggling with this for too long now.

test data.xlsx

Datasets

Accepted answers

lliberm01

I think i figured this out. If you break the workflow out into two sorted elements of quantity and amount, you can rank everything with multirow +1 for each and take the top 3 (dynamic) amounts in a formula tool by taking all that are less than or equal to the N variable (3). You can then break out whats left over in a filter and rerank with multitool and do the same process (top 2 [variable driven]). Once that is done, you can join everything up. Thanks. Just wish there was the ability to use a formula in the sample tool and this would all be short cutted.

All comments

sparksun

Could you confirm if the green part shown in the solution worksheet is consisitent with your described logic: “ the top 2 from any group that is left over”? Or could you further clarify your logic?

lliberm01

yes that was to note the top 2 that is left over among any group but for each year.

Yoshiro_Fujimori

Hi @lliberm01 ,

I followed your instruction but not match with your expected result.

I hope this may help.

(I worked on [Quantity] only, but you can apply the same logic to [Amount].)

workflow.yxzp

Qiu

@lliberm01
It turns out to be a big flow though the logic is not difficult. 😁

0102-lliberm01.yxmd

lliberm01

Forgot to mention this but the top 3 and top 2 are dynamic amounts (multiplying two other variables to get to this). Since you can't seemingly use a formula in the sample tool to create the N amounts, is there another way around this?

lliberm01

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167