Fill data in a column based on 2 condition

Help Activities
1

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

2

@Shilpa_Mohanty

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")
     Then 
          row(1) = "1"
      Else
          row(1) = "2"

  3. Finally use Write Range activity and pass DataTable InputDT to write back into Excel file.

3

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

4

@Shilpa_Mohanty

Also as an alternative you can check as below

image

  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

image
image1379×746 36 KB

image
image985×386 17.5 KB

Hope this may help you

Thanks

5

@Shilpa_Mohanty

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

6

Hi srinivas,

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

8

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

9

@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

10

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

11

Hi @Shilpa_Mohanty

Please try this below xaml,

Updatr.xaml (10.3 KB)

image

Sample Data,

image

Output

image

Thanks

12

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

image
image

13

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

14

it has to be done in uipath and not in excel

15

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

image
image954×612 20.8 KB

image
image1020×504 23.4 KB

image
image1301×468 21.7 KB

Can you able to access this xaml?

Updatr.xaml (11.2 KB)

Thanks

16

Hey,

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

17

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

18

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

19

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

Simple.
Also attached are process files.

image
image906×480 22 KB

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)

21

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