How to find the last row a specified value on a column?

Help
, ,
1

Dear Developers, i have a question a scenario where i have to find the last row of a specified value in a column and add two rows after that. hereby is my sample where i have to find the last row where word ‘ETO’ is located in Rank column and add two blank rows after that
PROB1
and the desired output should be like this…
PROB2
i know that using for each row and assign the column will give all the values from the column…but how to find the last row where specified word is located and two blank rows? your help will be very much appreciated. Thank You.

2

Create a second DataTable using build data table (with the exact same columns) but empty.

Iterate through your original datatable using a For Each Row

Use an If

If row(“Rank”) = “ETO”
Add Data Row with all of the columns as an array)
Add Data Row (with an empty array)
ADd data Row
Else
Add Data Row with all of the columns as an array)
End If

In that way you will be able to create a new data table with your spaces.

Did I explain myself correctly?

To add the fields, you can use the following arrays
{row(“No”).ToString, row(“Crew ID”).TOString, row(“Rank”).TOString, row(“Name”).ToString, row(“Crew Type”).ToString, row(“Nationality”).ToString}

and to add empty rows
{“”,“”,“”,“”,“”,“”,“”}

Thank you.

Kind regards,
Daniel

1 Like
3

image
image1227×499 21.8 KB

1 Like
4

DataTables.Rows.InsertAt method is to be used in your case, see the code that you need inside a Invoke Code activity (create in/out DataTable argument as inDT):

Dim index As Int32 = inDT.AsEnumerable.ToList.FindLastIndex(Function (row) row(“Rank”).toString.equals(“ETO”))
If index>-1
inDT.Rows.InsertAt(inDt.NewRow(), index + 1)
inDT.Rows.InsertAt(inDt.NewRow(), index + 1)
End If

1 Like
5

@Yusuf_Rahmaniac

Lets assume your datatableVarName is dtData

Find Last Index
dtData.AsEnumerable.ToList.FindLastIndex(Function (row) row(“Rank”).toString.Trim.equals(“ETO”))

Creating a new Empty row:
dtData.NewRow()

Inserting the empty Row
dtData.Rows.InsertAt
Use an invoke method activity and pass the empty row and Insert Position (LastIndex + 1) as arguments

3 Likes
6

Dear developers, thank you so much for the help. will try each of the solutions and get back to you all.

7

hi sir,thank you for your response. your solution add rows to every “ETO”. i wanted to add only to the last “ETO” of the cell.

8

you dont like mine? it takes just a little invoke code activity, and i tested it to be sure will work as you want… :sweat_smile:

9

Dear Yusuf,

Using the array, you can chose which values you want to add and change them if you want.

It is in the same order as it is in your Excel file.

Thanks!!

10

Oh sorry,

I misunderstood your question.

use a For Each Loop with a counter

Everytime it finds an “ETO” save the value in a variable (AUX_LASTETO) and then, reset the counter and add the condition if CONT=AUX_LASTETO add values, else, add normally.

Did I explain myself?

Thank you.

Kind regards,

11

Dear sir, Thank you so much for your response. but it throws an exception when i run it like below:

PROB1
PROB11150×592 36.4 KB

12

can i see your arguments for the invoke code?

13

data1
data1979×321 13.9 KB
data 2
data 21626×681 36.5 KB

hereby attached the screenshot sir!

14

it is not right…

  1. Dont create variable with same name as the argument
  2. Pass in the value of the inDT argument the variable of your datatable
15

hi sir, i did not create a variable. i only created an argument as the output for the datatable as seen on the screenshot

16

image

1 Like
17

oh yes i have just realised. thank u again for your help!:blush:

1 Like
18

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