Fill data in a column based on 2 condition

I have a column which has date format in dd/mm/yyyy .
I have to fill another column like, if year is 2018, bot should fill 1 in column b for all records having year as 2018 and if year is 2019, for all records having year as 2019 in that column, bot should fill 2 in column b and so on


Welcome back to our UiPath community.

  1. Use Read Range activity to read the data from Excel file and it will give output as DataTable. Let’s say InputDT.

  2. And then use For Each Row activity to iterate one by one row.

      For Each row in InputDT
         If row(0).ToString.Contains("2018")
              row(1) = "1"
              row(1) = "2"
  3. Finally use Write Range activity and pass DataTable InputDT to write back into Excel file.

Hey lakshman,

Thing is we can have n number of years and we have to read that also,
It wont be everytime 2018 or 2019
Its like if bot sees new year, it should update accordingly the column b


Also as an alternative you can check as below


  1. Use Excel Application Scope and give the File path of your workbook
  2. Use Write Cell activity and give the first cell say B1 in the above case and in the formula write as below, also use Auto Fill Range to copy to entire data

Hope this may help you



Can you tell more details about it for better understanding. What needs to updated ?

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


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


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


Now using lookup, you can retrieve the corresponding value

Maybe this not suitable solution, but just an idea

Hope this may help you


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)


Sample Data,





Format your column B as number and place this formula…


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)



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.


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


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

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