Extract a certain figure in excel without stating the cell position

[Katrina]

I want to extract the average spending of Aciel only, is it possible to do it without stating the exact cell position? Thanks.

[Gokul001 (Gokul Balaji)]

Hi @Katrina

To get the row Index

"A"+(index+2).ToString

Try this below expression to get the AVG

DtData.AsEnumerable(). Average(Function(row) Cint(row (1)))

(From d in dtData.AsEnumerable Where Not (isNothing(d(1)) OrElse String.IsNullorEmpty(d(1).toString.Trim))Select v = CDbl(d(1).toString.Trim)).Average(Function (x) x)

Regards
Gokul

[Palaniyappan (Palaniyappan P)]

Hi

Hope the below steps would help you resolve this

1. Use a read range activity and get the output as DATATABLE named dt

2. Then use a assign activity like this

stroutput = dt.Asenumerable().Where(Function(a) a.Field(of String)(“Daily Consumption”).ToString.Contains(“Average”)).CopyToDatatable().Rows(0)(1).ToString

For more details on DATATABLE and it’s expression

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 @Katrina

[ashwin.ashok ((TheCodingTheory))]

Hi @Katrina ,

Here is an approach which works for me.
First find the index of the Aciel using this:

Dt.AsEnumerable().Where(Function(w) w.ItemArray.Contains("Aciel")).Select(Function(s) Dt.Rows.IndexOf(s)).First()

Once you have the index, now you can read the range once more with range as

"A"+(index+2).ToString

Similar approach for finding the Average:

Dt.AsEnumerable().Where(Function(w) w.ItemArray.Contains("Average")).Select(Function(s) Dt.Rows(Dt.Rows.IndexOf(s))(1).ToString).First()

If you could provide a sample dataset, I can provide you with a sequence that encapsulates the entire logic.

Kind Regards,
Ashwin A.K

[Katrina]

Daily Consumption.xlsx (9.9 KB)

[ashwin.ashok ((TheCodingTheory))]

Hi @Katrina ,

Here are the results:

To retrieve index of Aciel →

dt_sampleData.AsEnumerable().Where(Function(w) w.ItemArray.Contains("Aciel")).Select(Function(s) dt_sampleData.Rows.IndexOf(s)).First()

The Range is read from Aciel onwards and overwritten into our DataTable variable.
Next, we retrieve the Average from Aciel →

dt_sampleData.AsEnumerable().Where(Function(w) w.ItemArray.Contains("Average")).Select(Function(s) dt_sampleData.Rows(dt_sampleData.Rows.IndexOf(s))(1).ToString).First()

GetDataBasedOnKeyword.xaml (6.5 KB)

Kind Regards,
Ashwin A.K

[Katrina]

Solved, thx a lot.