Get row under current row in datatable/excel

Help
1

Lets say that I have a datatable that looks like this:

image

I for example need to compare the row where the date is 2020-09-24(row 4) with the row under(row 5) in this case.

How do I make it so that the current row that have the cell value I need compares to the row under?

TLDR; I always need to compare current row with the row under.

Row 3 meets the condition and now needs to take row 4 and put it in a new datable or just compare it in the same datable. I need to work with Splits or Linq?

2

@atomic

Check below post

Hope this helps you

Thanks

3

I think that is a little bit different. You are manipulating specific cells.

I will try to explain again.

  1. My list:
    image
  2. If the row contains 2020-09-24 I want the following output into a new datatable:
    image

So I always need the row that is under the row that meets the condition merged into a new datatable.

4

@atomic Is the date(2020-09-24) static or will change as per current date?

5

@Vrushali_Gave The date will change for every transaction :slight_smile:

I have another datatable with other values where I also get the dates which I later use with another datatable and so on.

6

@atomic Okay, could you elaborate more ? On what bases you are taking a particular date for comparison?

7

@Vrushali_Gave Is there a way to extract the current(if it meets the condition in the specific cell, lets say “B4”) row and the row directly under into a new datatable?

like i described in the post with the 2 pictures above.

If you need more information I will gladly provide it.

8

@atomic - which part are you having trouble with? The selecting of the row where it has the value you found? Or are you having trouble getting the next row portion?

If it’s the latter and you already have the datarow, you can use the row index to get the next row. NextRowIndex = MyTable.IndexOf(RowThatMatches) + 1 this finds the index of the current row and adds 1. Then you can reference the next row easily: NextRow = MyTable.Rows(NextRowIndex)

EDIT: Whenever you are using a “previous row” or “next row” sort of solution, you’ll want to include an if statement so you won’t try to get a “previous row” on the first row, or “next row” on the last row

9

@Dave I’m at this stage right now:

image
image578×672 17.2 KB

I am using the For Each Row activity where I then Use the If Activity. The column(“Fr o m”) needs to match the dynamic date that I contain in the variable “dateA”.

I have problem with the “Then” part. I see that you have described it for me with

NextRowIndex = MyTable.IndexOf(RowThatMatches) + 1
and
NextRow = MyTable.Rows(NextRowIndex)

I am little bit confused how I should finish the code so I go from this datatable: image

and get this result: image

Instead of “Fr o m” think “Date”.

10

Thank you for the detailed responses and questions that include pictures - it is extremely helpful!

Since you want to put it into a new datatable but keep all the headers the same, you should use Datatable.Clone() before going into your for each loop so the datatable is ready to go. This is done with a simple assign activity right before the for each row loop: Assign MyNewTable = ExtractDataTable.Clone

In your if statement, NextRowIndex should be an integer variable. The right side would be: ExtractDataTable.IndexOf(row) + 1 This is getting the index of the current row and adding one. However, since you’re doing a for each loop, you can just get the index from the activity itself - the properties has an optional output called row index (or somethign similar). You can just output to a variable and call it CurrentRowIndex or something similar. For my example below, i will assume you don’t separately assign NextRowIndex and only use the CurrentRowIndex (since that’s what i recommend).

In the TRUE side of your if statement you can use 2 invoke method activities to import the rows into the new datatable. See here for a good example with screenshots on how to do it: Copy Row from one DataTable to another - #2 by UiRobot

The parameters for the first row would be just like the screenshots linked above. The second one you should alter the ‘value’ of the paramater from row → ExtractDataTable.Rows(CurrentRowIndex + 1)

1 Like
11

Thank you so much.

  1. Did this:
    image
  2. I think I am almost there but I currently get the same datable but with the result appended at the bottom haha.
    image
    image1008×815 39.7 KB

EDIT: Do the Target Object need to be a new datatable that I have not created yet?
Or can I just clear the current datatable(ExtractDataTable).

12

So close! Just update the TargetObject to be the new datatable. Make sure to do the ExtractDataTable.Clone() on the new datatable before this For Each Row activity as mentioned above.

1 Like
13

Thank you so so much :slight_smile:

1 Like
14

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