Sort 3 values from 3 different columns by size for each row

Help Studio
,
1

Hello everyone, I am looking for a solution to the following problem.

I have 3 columns in Excel and am looking for a way to find out which of the values ​​per row is the largest, which is the second largest and which is the smallest

grafik

Thank you very much and hope you can help me

2

Hi @sBaDE

  1. Read Range Activity as Assign Variable as DT DataTable
  2. For Each Row in Data Table Activity AND Assign Three Variables LargestValue, SecondLargestValue, SmallestValue as Integer

LargestValue = row.ItemArray.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MinValue)).Max()

SecondLargestValue = row.ItemArray.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MinValue)).OrderByDescending(Function(x) x).Skip(1).FirstOrDefault()

SmallestValue = row.ItemArray.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MaxValue)).Min()

Hope it’ll helps you :slight_smile:
Cheers!!

3

Hello there !

Please find my solution in attachment.
Result is put on another sheet called “Result”

BlankProcess8.zip (69.5 KB)

Hope this helps

4

First of all, thank you very much for your help :slight_smile:

The screenshot is only a small excerpt of the entire Excel file. This includes columns from A:CX, of which only the three columns AD:AF contain the values ​​from which I have to find the largest, the second size and the smallest value per row. So if I choose the RowItem example, it would check every number, but should only refer to the 3 columns.

So I have to find the three values ​​in each row for about 70k rows in the Excel file

1 Like
5

@sBaDE

You can define Column

{row(columnIndex1), row(columnIndex2), row(columnIndex3)}.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MinValue)).Max()

{row(columnIndex1), row(columnIndex2), row(columnIndex3)}.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MinValue)).OrderByDescending(Function(x) x).Skip(1).FirstOrDefault()

{row(columnIndex1), row(columnIndex2), row(columnIndex3)}.Select(Function(x) If(IsNumeric(x), Convert.ToInt32(x), Integer.MaxValue)).Min()

Hope it helps :slight_smile:

1 Like
6

Hi @sBaDE ,
I have question your query,
image

14,5 is decimal number or anything else.

Regards,
Darwin

7

Thank you. It worked. I just had to make an adjustment from Convert.ToInt32 to Convert.ToDouble

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.