Fill data in a column based on 2 condition

Hi srinivas,

How can i give the formula as year wont be static.
We have to read different year and then update accordingly

In year column, we can have n number of years like 2018, 2019, 2020 and so on.
And then every year can have multiple data like year 2018 can have 5 rows ,2019 can have 7 rows and 2020 can hav 2 rows and for every unique year we can have few rows.
So for all unique values of year we have to update column b starting from 1
For all records of 2018, column b should be 1
For all records of 2019, column b should be 2
For all records of 2020, column b should be 3
And so on

@Shilpa_Mohanty

One way of doing is keeping a year mastersheet which contains 2019, 2020, 2021 as below

image

Now you can place a column in the sheet where you can extract the year by as below

image

Now using lookup, you can retrieve the corresponding value

Maybe this not suitable solution, but just an idea

Hope this may help you

Thanks

I wont be knowing the years un advance so this wont help

Hi @Shilpa_Mohanty

Please try this below xaml,

Updatr.xaml (10.3 KB)

image

Sample Data,

image

Output

image

Thanks

Format your column B as number and place this formula…
=RIGHT(A1,4)-2017

image
image

without using invoke code, can we use anything else as we are not allowed to do that
and this excel I am not able to access it shows invalid file

it has to be done in uipath and not in excel

Please try like this,

First For each
Code Used -

dt.AsEnumerable.Select(function(d) d(0).ToString.Split("/"c)(2)).Distinct

assign variable counter in index argument
Type Argument - String

Second For each
Code Used -

dt.AsEnumerable.Where(function(row) row(0).ToString.Split("/"c)(2).Equals(item))

Type Argument - System.Data.Datarow

Can you able to access this xaml?

Updatr.xaml (11.2 KB)

Thanks

Hey,

As my excel may contain more than 20000 records, can you help me with linq query instead of for each

May i know why you cannot use invoke code, the first method will not loop through all the rows, instead it will loop through only the distinct year values.

Thanks

Custoner wants linq query and no other excel activities to be used.

@Shilpa_Mohanty

it looks like your case can be solved with grouping the data on year with the help of a groupby LINQ statement or the non-linq approach. Both options are described here:

for updating the data following options out of many options are available:

  • dynamic creation of a lookup dictionary - updating within a for each
  • LINQ approach with data table reconstruction

Simple.
Also attached are process files.

Fill data in a column based on 2 condition -Forum.xaml (10.0 KB)
Fill data in a column based on 2 condition.xlsx (8.5 KB)

My excel may contain more than 20000 rows. Is there any other way without using for loop as it will consume more time

@Shilpa_Mohanty
find some starter help here:

it is using LINQ for grouping the data and used the for each index out put for the flagging
grafik

XAML here:
Shilpa_Mohanty.xaml (10.2 KB)

from RnDs, processing time mesaures using for each in a core way is NOT to avoid.
Thats why the hybrid approach (do grouping with LINQ, update with for each) is well balancing

  • the compact way of LINQ
  • the maintainability of for each along the debugging options

we would recommend this approach.

For a LINQ only approach we would request some data samples with the exact data column structure.


getting this error

do following:
grafik

Tested this code for more than 20000 records. You may need to fine tune selectors and delays to your need. Assumed that there are no blank entries in column A.
It’s like magic. The real power of automation.

image
Fill data in a column based on 2 condition.xlsx (1.1 MB)
Fill data in a column based on 2 condition (2).xaml (16.7 KB)

thanks for the response
but here we cant use write cell. we have to play around using linq query and datatable