How to rank a list of numbers.(Power Automate Desktop)
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
END
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.
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | Value | Rank |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | 1 | 1 |
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).
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | %CurrentItem% | %'=RANK.EQ(A' + RowCounter + ',A2:A' + (NumList.Count + 1) + ')'% |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | %RowCounter% | %RowCounter% |
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.
| Parameter | Value |
|---|---|
| Retrive | Value from a range of cells |
| Start column | 1 |
| Start row | 1 |
| End column | 2 |
| End row | %RowCounter - 1% |
| First line of range contains column names | ON |
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.