Get highlighted data into a datable

Help Activities
, ,
1

I have a report sheet as below.

image
image1067Ă—680 11.6 KB

I need to get the data into a data table based on the ticket number.

For example if I have the ticket number as : 14876 then I need to get rows starting ticket number to total all rows into a data table. { 3rd highlighted section}

If the ticket number is: 123456 then I need to get rows starting ticket number to total all rows in data table. {1st highlighted Section)

I am able to get the ticket number data row index using : lookupdatatable activity.
I need to get the total row index immediately after the corresponding ticket number.

How do I achieve this thankyou.
attached a sample excel file below
test.xlsx (9.3 KB)

@ppr @Yoichi

2

@Sairam_RPA

  1. dt.AsEnumerablw.Where(function(x) x(0).ToString.Equals("Total")).Select(function(x) dt.Rows.IndexOf(x)).ToArray() this will give the row numbers of all the total related rows…
  2. From the lookup table you know the start row number…now find the closest total which is after the identified lookup row…
  3. then copy those rows to a separate datatable…dt.AsEnumerable.Where(function(x) dt.Rows.IndexOf(x) >= startRow AndAlso dt.Rows.IndexOf(x)<TotalArray.Where(function(x) startRow<x)(0)).CopyToDataTable

Hope this helps

cheer

3

How do I get the closest total row index after the start row number ? I have thousands of rows of data.

4

@Sairam_RPA

The cormula in the step 3 gives you the required rows…please check…

The where consition is for the same

Cheers

5

I used this formula as the formula you provided was throwing errors.

Dim arrInt() As Integer = dt.AsEnumerable().Select(Function(r, i) If(r(0).ToString() = “Total”, i, -1)).Where(Function(i) i >= 0).ToArray()

To get all row indexes for “Total”

Then I use the following expression to get the values of the data table I need.

dt.AsEnumerable.Skip(startrowIndex).Take(endRowIndex - startRowIndex + 1).CopyToDataTable

This has resolved my issue.

Thankyou @Anil_G & @Vikas_M for your valuable inputs

1 Like
6

@Sairam_RPA

This also would work…once you get the array of row indexes now…you can use the formula like this to get the required rows

dt.AsEnumerable.Where(function(x,i) i >= startRow AndAlso i<TotalArray.Where(function(x) startRow<x)(0)).CopyToDataTable

cheers

1 Like
7

Hey @Sairam_RPA ,

Please refer below file
Excel_Remove_Blank_Rows.zip (10.3 KB)

I have created a dictionary called as dt_dict which is of type <String, Datatable>will have your ticket number as key and The values below the ticket number row as datatable until a blank row is met

hope it helps you out!

1 Like
8

Thankyou @Vikas_M. This is helpful

9

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