Comparing multiple choice answers and assigning them in excel as n largest

[smehro]

Hi,

I am trying to achieve 2 highest, 2 high, 2 medium, 2 low and 2 lowest numbers from a row in excel online. The row is the responses from the users by selecting an option from multiple choice answers. For example:

Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10
6	5	4	4	4	2	5	1	3	2

In above table, highest number would be 6 (Item 1) followed by 5 (Item 2 & 7). In this situation, I need 2 highest numbers but currently I just have 1 and need to decide which would be 2nd from Item 2 or 7. For this, I ask the user, would you prefer Item 2 or Item 7. Suppose, you says 'Item 7'. With this, I get my 2 highest values as Item 1 & Item 7. [As a consequence when user says 'Item 7', i can either increase value of item 7 with 0.5 so that it becomes 5.5 and clearly 2nd highest.] Similarly, there would be comparison for 2nd high number between Item 3,4 and 5.

Now, Is it possible to do this in PVA actions by marking conditions? OR Is there a way to do this on excel in real time as responses are received?

(PS: I tried doing this in PVA conditions using bubble sort logic but it seems brute force and there would be so many conditions.) 

@renatoromao @ChristianAbata @kaznakaxx 

[renatoromao · Accepted solution]

Hi @smehro ,

You can do it using the Power Automate, creating the workflow to get all items and current values, and showing to the user using a Markdown detail: https://docs.microsoft.com/en-us/power-automate/approvals-markdown-support

But unfortunately, when you will show the options to the user like "Item 2 or Item 7" to select one option of the 2nd, you cannot use the options because today the PVA not allow external values from options question.