Row index value

Help Activities
1

Hi all,

I have an excel with 2 columns A and B. I need to filter B column with “ABC”. And if “ABC” I’d found in B column I want to replace that value with Column A data.

Example if I find “ABC” in 2nd row of B column then I need to replace it with Column A 2nd row value.

I am filtering the data and passing that filtered data into for each row but how do I get the row where exactly i need to replace the data.

Can you please help me

1 Like
2

Hi @0bb4628e217fd43ac86ac9294

You can use the LINQ Expression for this, check the below steps,

→ Use read range workbook activity to read the excel and store in a datatable called dt_Input.
→ Then use assign activity to write the linq expression,

- Assign -> dt_Output = (From row In dt_Input
                         Let ColumnB = If(row("ColumnB").toString.equals("ABC"), row("ColumnA"), row("ColumnB"))
                         Select DT1.Clone.Rows.Add({row("ColumnA"),ColumnB})
                                      ).Copytodatatable()

→ Use the Write range workbook activity to write the dt_Output to same excel.

Check the below workflow for better understanding,

Regex_Practice
Regex_Practice847×580 35.1 KB

Hope it helps!!

1 Like
3

If you want to do with excel activities, then follow the below steps… @0bb4628e217fd43ac86ac9294

→ Take the Excel Process scope activity and insert the Use excel file activity inside of it.
→ Give the Path of the excel in Use excel file activity.
→ Insert the for each excel row activity inside the Use excel file activity.
→ Inside for each insert the If activity to check the condition, give the below condition in If,

- Condition -> CurrentRow("ColumnB").equals("ABC")

→ In Then block insert the write cell activity, In What to write field give the CurrentRow(“ColumnA”).toString and In where to write field give the CurrentRow.ByField(“ColumnB”).
→ Don’t insert any activity inside the else block.

Check the below workflow for better understanding,

Regex_Practice
Regex_Practice900×1536 108 KB

Hope it helps!!

1 Like
4

@mkankatala Thank you for such a fast reply but I am facing with an issue that is I have other columns also like C to Z after giving this linq I am getting all the other columns empty.

1 Like
5

And coming to excel application scope I am getting error in the range which we have provided CurrentRow.By Field is not working in my case

1 Like
6

Could you share your input file and give me the details which columns we have to check according to that I’ll modify the linq expression… @0bb4628e217fd43ac86ac9294

Hope you understand!!

1 Like
7

It will works fine. Could you send me a screenshot how you are giving in your workflow… @0bb4628e217fd43ac86ac9294

1 Like
8

@mkankatala Sorry for the late reply thanks for your help.

2 Likes
9

It’s my pleasure… @0bb4628e217fd43ac86ac9294

Happy Automation!!

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.