How to rank a list of numbers.(Power Automate Desktop)


Japanese version.

Here is how to rank the numbers in the List. The result is set in a Data table.

Overall view of Flow

Robin(for copy and paste)

It can be copied and pasted into Power Automate Desktop.

SET NumList TO [2, 5, 10, -8, 6]
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: False Instance=> ExcelInstance
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Value''' Column: 1 Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Rank''' Column: 2 Row: 1
SET RowCounter TO 2
LOOP FOREACH CurrentItem IN NumList
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem Column: 1 Row: RowCounter
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: '=RANK.EQ(A' + RowCounter + ',A2:A' + (NumList.Count + 1) + ')' Column: 2 Row: RowCounter
    Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: 2 EndRow: RowCounter - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance

Flow creation steps

Prepare a List of the numbers you wish to rank.

Set up Launch Excel Action and use with a blank document to create an empty Excel book.

Create the first row of an empty book created by setting up two Write to Excel worksheet Actions.

The value to be written is the column name. In the example, it is Value and Rank, but it is arbitrary.

ParameterValue of the first ActionValue of the second Action
Value to writeValueRank
Write modeOn specified cellOn specified cell

Set up Set Variable and create a variable %RowCounter% and set the initial value to 2.

Then loop through List variable %NumList% with For each.(Specify List variable %NumList% for Value to iterate)

Place two Write to Excel worksheet Actions in For each.

The second value to write is the Excel RANK.EQ function.

If you change the value here, you can change the ranking method (Change to the RANK.AVG function) or switch the ranking to descending order (Add an argument to the RANK.EQ function).

ParameterValue of the first ActionValue of the second Action
Value to write%CurrentItem%%'=RANK.EQ(A' + RowCounter + ',A2:A' + (NumList.Count + 1) + ')'%
Write modeOn specified cellOn specified cell

Then place Increase variable Action in For each to count up %RowCounter% by 1.

The For each part of the flow is complete. The flow is as shown below.

Under For each set up Read from Excel worksheet Action and configure as follows.

RetriveValue from a range of cells
Start column1
Start row1
End column2
End row%RowCounter - 1%
First line of range contains column namesON

Finally, close the book with Close Excel Action. There is essentially no need to save.

When this flow is executed, the Read from Excel worksheet Action The numbers and ranks are stored in the Variables produced (Variables name %ExcelData% by default).

As an example, the Data table below would look like this for %[2, 5, 10, -8, 6]%.

For those who want to learn Power Automate Desktop effectively

The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Power Automate Desktop to streamline your workflow.


Tips(Power Automate Desktop)