How to get the Max and Min value from excel and write it to another excel

[Krystal]

Hi! I would like to extract the maximum and the minimum value from one excel but there is no column name for it. Then write the max and min value with the company name on another sheet. Can someone guide me accordingly? Thank you.

[ashwin.ashok ((TheCodingTheory))]

Hi @Krystal ,

Could you try unchecking Add Headers and use these two snippets of code?

Min->

Dt.AsEnumerable().Min(Function(m) Convert.ToDouble(m(1).ToString.Trim))

Max →

Dt.AsEnumerable().Max(Function(m) Convert.ToDouble(m(1).ToString.Trim))

Kind Regards,
Ashwin A.K

[Palaniyappan (Palaniyappan P)]

Hi

Welcome to UiPath forum

I would like to share you a tutorial where we have all functions related to datatable
Hope it would be useful for you

We have included a expression to know the max and min value of a column

Get Maximum value of a column in datatable

Use a assign activity like this

int_max = dt.AsEnumerable().Max(Function(row) cint(row(columnindex)))

(Output - int32 type)

Get Minimum value of a column in datatable

Use a assign activity like this

int_min = dt.AsEnumerable().Min(Function(row) cint(row(columnindex)))

(Output - int32 type)

**Where column index starts from 0 for first column and 1 for second column and it goes in that order

If you don’t have ColumnName we can make use of column index**

For more references

All about Datatable - UiPath

Hi All, Datatable - variable type is the most commonly used variable type by all UiPath users And it’s been the most often asked queries in forum especially on its manipulation So I would like to have this post as a repository for those who are looking for expressions related to Datatable Let’s go one by one keeping dt as name of datatable variable 1. Get number of rows in a datatable dt dt.Rows.Count (output - int32 type) 2. Get number of columns in a datatable dt dt.Columns.Count (output…

Cheers @Krystal

[ppr (Peter Preuss)]

@ashwin.ashok

Dt.AsEnumerable().Min(Function(m) Convert.ToDouble(m(1).ToString.Trim))

to combine it:

arrOrderedValues | Int32( ) or Double( ) if needed
=
Dt.AsEnumerable().Select(Function(x) Convert.ToDouble(x(1).toString.Trim)).Orderby(Function (x) x).toArray

we get the min by: arrOrderedValues.First()
we get the max by: arrOrderedValues.Last()

[Krystal]

I can the value successfully but how can I write the value and the related company name to another excel sheet?

[Palaniyappan (Palaniyappan P)]

Fine

I do have another approach since you want the corresponding row as well

1. Let’s say you have the datatable named dt

Now use a Build datatable activity and create two columns one with first columnname Name and another ColumnName as value and get the output as Finaldt

Then use a clear datatable activity and pass Finaldt as input

2. Use sort datatable activity and pass dt as input and mention order as ascending to get the minimum value and index as 1 and Get the output as same variable dt

3. Now use a Add Datarow activity and pass Datarow as dt.Rows(0) and in datatable mention as Finaldt

4. Again use a sort datatable activity and pass dt as input and order as descending and index as 1 and get the output as dt

5. Finally use another add Datarow activity where mention Datarow as dt.Rows(0) and datatable as Finaldt

6. This Finaldt will now have both column values with min and max value

Use write range activity and enable add headers and mention the sheetname you want

Cheers @Krystal

[supermanPunch (Arpan)]

@Krystal

Hi @Krystal ,

For the Input data provided, the Output Should be the Min and Max Value rows.

So In short, the Output should contain only two rows.

For this You can follow the Below Steps.

1. You have the Datatable read using Read Range Workbook Activity, let’s say having the datatable variable named DT.(Without Add Headers)
2. You can then create a Clone of this DT, with variable say cloneDT. Clone can be achieved as below using Assign Activity :

cloneDT = DT.Clone

3. Next, We can use two Add Data Row Activity to add the min and max value row. Finding the min and max value rows can be done as below :

DT.AsEnumerable.OrderBy(Function(x)Cdbl(x(1).ToString)).First.ItemArray  //Row having Minimum Value

DT.AsEnumerable.OrderBy(Function(x)Cdbl(x(1).ToString)).Last.ItemArray  //Row having Maximum value

Using the Add Data Row Activity twice :

[Krystal]

I have tried but there is an error. What’s wrong with my robot?

[Palaniyappan (Palaniyappan P)]

Fine instead of Datarow let’s use ArrayRow

Mention like this in both add Datarow activity

In ArrayRow property

{dt.Rows(0)(0).ToString, dt.Rows(0)(1).ToString}

And in datatable mention as Finaldt

Remove what is mentioned in Datarow

You are almost done

Cheers @Krystal

[Krystal]

Thanks for your help!

[Krystal]

Thanks a lot.

[Palaniyappan (Palaniyappan P)]

Glad it got resolved @Krystal