How to change excel cell value base on the adjustment criteria stated in another excel sheet?

[Krystal]

How to change excel cell value base on the adjustment criteria stated in another excel sheet?
I have 2 excel sheets now, if “Type” column in sheet 1 is same as the “Type” column in sheet 2, then “Level” column in sheet 1 will be increasrd base on “Addition” column in sheet 2. If not the same, then pass

E.g. C4 in sheet 1 will increase by 2 and become 4
Here is the result that I want:

file.xlsx (10.2 KB)

[Shahabuddin_Nadeem]

Hello Krystal,

Kindly follow the below step, it should do the job.

1. Create a dictionary(DC) variable with “Sheet2”.
2. Create a datatable (DT) variable with “Sheet1”. [Use the correct range as given sheet have some extra rows with no data]
3. Loop through the DT variable and assign level column as below
   CurrentRow(“Level”) = Cint(CurrentRow(“Level”)) + Cint(DC(CurrentRow(“Type”)))

I hope, it helps.

[Krystal]

How to create a dictionary variable with “Sheet2”?

[Shahabuddin_Nadeem]

Hello Krystal,

Below is the approach. Make sure the excel sheet is in same directory of this xaml file.
ExcelUpdate.xaml (13.7 KB)

If this solves your query, kindly mark this as solution.

I hope, it helps.

BR,
Shahabuddin N

[Krystal]

Thank you for your suggestion. I want to ask how to write the Condition if I use “If activity” instead of “TryCatch” to get the same result?

[Shahabuddin_Nadeem]

Comment out the “TryCatch” activity and add “If activity” as per below.

I hope, it helps.

BR,
Shahabuddin N

[Krystal]

It works with this excel file. However, when I apply the same method to another excel, it shows “Assign: Conversion from type ‘DBNull’ to type ‘Integer’ is not valid.”, this error appears in creating dictionary variable. What is the problem?

[Shahabuddin_Nadeem]

“Addition” column in Sheet2 might be having some null values and for integer, it is not valid value. Below are the two option you can chose.

1. As this column is for adding the level in sheet1, it can’t be null. you can assign 0 instead of null.
2. Wrap this assign activity with check on value of “Type” and “Addition” not to be null and then create the dictionary variable (below is the change).

Also, make sure the column name is not having any extra space.

I hope, it helps.

BR,
Shahabuddin N

[Krystal]

I appreciate your help very much, it works. I have a further question. I want to adjust “Level” column in sheet 1 if the Name column in sheet 1 contain certain word listed in sheet 3, then increase the value base on “Add” column in sheet 3. How to write the Condition inside “if activity”?

file.xlsx (11.1 KB)

[Shahabuddin_Nadeem]

What i’ve understood, is you want exact same behavior of Sheet2 over Sheet1 but using Sheet3, instead of Sheet2. if that is correct, below is way to do it.

Logic remain the same, you have to modify Read Range and For each activity.
Just update the dictionary variable creation.

1. Update the Read Range activity from Sheet2 to Sheet3
2. Change Column name “Type” to “NameAdj”
3. “Addition” to “Add”
4. Rest all will remain the same.

I hope, it helps.

BR,
Shahabuddin N

[Krystal]

I have tried, but the “Level” column did not change.
Below is the result that I want:

[Shahabuddin_Nadeem]

I think, i got it wrong. This time, the validation column is NAME, not TYPE.
Just confirm one thing, the matching text will always be the last string in NAME (ABC share)column or it can appear anywhere?
Also, text under NAME column will always be unique or not?

[Krystal]

The matching text will appear in any position and the text under NAME column will always be unique. And I have added more adjustment criteria in sheet 3, how to deal with it? Thank you.
file.xlsx (11.3 KB)

[Shahabuddin_Nadeem]

This will do the exact what you are looking for.
ExcelUpdate_Sheet3_V1.0.xaml (16.4 KB)

I hope, it helps.

BR,
Shahabuddin N

[Krystal]

My problem is solved, thank you so much!