Rank in Order of Priority by Workgroup

MOO

Hello,

Thank you for any help in Advance

I am attempting to force a ranking priority based on each workgroup. So far the RANKEQ is not sufficient to rank based on group.

For instance:

If a team member submits a request under "referrals" They have to rank their request against other referral requests they have placed in previously. The ranks would not apply to "Scheduling" or any others as they will have their own individual ranks. I would also like the inability to duplicate ranks in a workgroup, so that they are forced to prioritize. Also if there is a way for the rank to update once a row is completed that would be great.

Any information anyone can share would be so helpful.

Thank you!

Genevieve P.

Hi @MOO

You can use a COLLECT Function within a RANKEQ Function to filter and apply the rank within a criteria:

=RANKEQ([Number Column]@row, COLLECT([Number Column]:[Number Column], WorkGroup:WorkGroup, WorkGroup@row), 1)

That said, I'm not quite sure what you're applying a rank to, is it the "Workgroup by Number" column? If you'll have duplicate values to rank by, check out this other Community thread: Ranking without Duplicating Ranks

Let me know if this helps!

Cheers,

Genevieve

MOO

Thank you Genevieve, I will take a look at this to see if i works for me. I am attempting to rank the 'metrics" column by "Workgroup" or "Workgroup by number".

Thank you!

MOO

I was able to use this formula but it still did not rank by group. It ranked the whole sheet instead.

=RANKEQ([Workgroup by Number]@row, COLLECT([Workgroup by Number]:[Workgroup by Number], Metric:Metric, Metric@row),1)

How may I tweak this to rank by the "workgroup by number" or "Workgroup"?

Thank you for any assistance.

Genevieve P.

Hi @MOO

It looks like you might have this swapped around. You'll want to first list the column/value you're looking to RANK (so the column that contains your numbers, which sounds like this is your Metric column). Then in the Collect function you filter by the Group, identifying that you want a different ranking system per-group.

Try:

=RANKEQ(Metric@row, COLLECT(Metric:Metric, Workgroup:Workgroup, Workgroup@row), 1)

Keep in mind this means the Metric column needs numerical values and I assume your Workgroup column has the Workgroup categories you want to rank.

Does that make sense?

MOO

Hi @Genevieve P.

This actually worked when I used random numbers in the metric column. Unfortunately my metric column is not numbers. It is the name of a metric, so it needs to be free text. For example: "The number of patient admissions".

Is there anyway I can use this on a text column or is there something similar?

Thank you so much for your help so far!

Genevieve P.

Hi @MOO

How are you deciding the "rank"? What value determines the rank? So if you have "The number of patient admissions" is that higher or lower than "Report", and how do you know?

The RANKEQ function creates a ranking based on a numerical value. We could rank by the row number, so by the submission time?

MOO

@Genevieve P.

The rank is ultimately decided by each workgroup leader. So each team leader places a new row for a metric they want developed, however we need them to rank order of priority against their existing requests already on the sheet.

I can see how the default will be row number though. Maybe I can work with that first as I have an existing excel spreadsheet in use.

Genevieve P.

Hi @MOO

Another alternative would be to automatically assign a Number value to each of the possible Text selections.

For example, if the metric is "Number of Patient Admissions" then that's automatically number 1. If it's "Report", it's automatically 2, and so on. Then you can Rank off of this helper column that assigns the number value. Does that make sense?