Validating datatable on excel

Help Activities
, , ,
1

hai, i realy need your help. i have some problem in my activity. i really need your advice for may activity. i have some data like this.

Screenshot 2023-07-25 at 17.07.29
Screenshot 2023-07-25 at 17.07.29810×426 48.7 KB

I want to give the results of the last position of the person as shown in the image below.

Screenshot 2023-07-25 at 17.07.13
Screenshot 2023-07-25 at 17.07.13808×424 58 KB

what do I have to do? Thank You

2

Hi @wsinten

Could you elaborate your query then we will get better understanding of it.

3

Hi @wsinten

On what basis are you writing Outside? What is the condition by which you’re writing Last Location as Outside?

4

viewed from the “Person ID” column. because if the value in “person ID” is an even number then the result of the last position is definitely outside (in the last location column it is filled with “OUTSIDE”). but if the number of Person IDs is odd (1,3,5) then the last one must still be in the room. therefore it is filled with “Outside” only a few lines before the last one as shown in the picture sir :pray:

5

based on the odd or even number of person IDs. if the number is odd, then only a few lines before the last as shown in the image. if the number is even, then all of them are written “OUTSIDE”

6

In this case

=> Use the Use excel file activity and give the path of the excel.
=> Use for each excel row inside the Use excel file activity to iterate the each row in the excel.
=> Inside for each use If condition to check the Person ID is even or add. Then give the below condition.
CInt(CurrentRow(“Person ID”)) mod 2 = 0
In then block give the write cell activity to write the Outside in Last location column.
In else block give the another write cell activity to write the Inside in the Last Location Column.

Note - In Write cell activity give like this Currentrow.ByField(“Last Location”) and check the Auto increment row option.

image
image880×1714 57.2 KB

Check the below xaml file-
Browser_Practice.xaml (16.8 KB)

Output excel file -
image

Hope it helps!!

7

Thank you for the help. but sorry, if the data being checked is in the thousands, won’t it take a lot of time, sir?

8

As an alternate you can work on a datatable base and then write it back to excel. The main building concept is about grouping the data on the name column

  • read range - dtData

  • Assign Actvitiy
    OutSideRows | List(Of DataRow) =

(From d in dtData.AsEnumerable()
Group d by d("Name").toString.Trim into grp=Group
Let isEven= CInt(grp.Last()("Person ID").toString.Trim) Mod 2 = 0
Select grp2 = If(isEven, grp.toList, grp.SkipLast(0).toList)).SelectMany(Function (x) x).ToList

  • For each activity | item in OutSideRows | TypeArgument: DataRow

    • Assign Activity: item(“Last Location”) = “OUTSIDE”

  • Write range | dtData

We recommend to write it back to another worksheet especially for the first test rounds

9

Yes @wsinten

Then better to use the LinQ query check the below one.

(From d In dtData.AsEnumerable() Let personID = CInt(d("Person ID").ToString().Trim()) Let isEven = personID Mod 2 = 0 Let resultValue = If(isEven, "OUTSIDE", "INSIDE") Select d.Field(Of String)("Last Location") = resultValue).CopytoDatatable()

Hope it helps!!