How to find out the Column values are less than 40

RPA Discussions
#1

Hi,
How to find out the below marked subject is less than 40.
if less than 40 then count could be 1, 2, 3 etc

Kindly assist.

image
image963×241 15.9 KB

#2

@VJK

Welcome to forums

You can use Write cell activity and write CountIF expression

Check below for CountIF

Hope this will help you

Thanks

How to loop through each row in excel and increase count if row has marks < 40
#3

Hi @VJK ,

I do think there is Still not enough information as to How you would require the Output to appear.

Could you also provide the Expected Output for the Input Excel Shown ?

#4

Hi @VJK ,

Assuming that you want to assign the count of failed subjects to At_Kt Column, you may use an Invoke Code to achieve it →
image

dt.AsEnumerable().ToList().ForEach(sub(row) row("At_Kt")={"English", "Urdu", "Physics", "Math", "Chemistry", "Biology"}.Select(Function(s) If(CInt(row(s).ToString)<40,1,0)).Sum)

Benefit of using an Invoke Code is that you don’t have to worry about specifying all columns - you can simply focus on those columns that you want to work with.

GetBelowFortyCount.xaml (8.4 KB)

Kind Regards,
Ashwin A.K

#6

This is look like hardcoded.

#7

Hi @VJK ,

Have you tested the sequence shared above?
Replace the Build DataTable Activity with a Read Range Activity and please let me know if it works as expected.

Kind Regards,
Ashwin A.K

#8

@Ashwin,
Not working as expected.
I am sharing the sheet,
Kindly help to print number of count in Al_KL columns if subject mark is less than 40
and rank will be based on mark.

image
image920×180 5.88 KB

#9

Hi @VJK ,

We’d appreciate it if you could provide the sample dataset so that we can create a proper end to end resolution for you, could you please do the needful?

Kind Regards,
Ashwin A.K

Assign rank to each student based on its Total Marks
#10

@Ashwin,
I am unable to upload file here due to new user.

Given below the format for data set and expected result in Al_KT and Rank columns

image
image924×158 9.95 KB

#11

Hi @VJK ,

Could you give this a try?

Dim arr_subjects = {"English", "Physics", "Chemistry", "CS"}
Dim arr_marks = dt.AsEnumerable().Select(Function(s) arr_subjects.Sum(Function(su) CInt(s(su).ToString))).OrderByDescending(Function(o) o).ToArray()
dt.AsEnumerable().ToList().ForEach(Sub(row) 
	row("at_kt")=arr_subjects.Select(Function(s) If(CInt(row(s).ToString)<40,1,0)).Sum
	row("Rank") = Array.IndexOf(arr_marks,arr_subjects.Sum(Function(su) CInt(row(su).ToString)))+1
End Sub)

GetBelowFortyCount_v1.xaml (8.4 KB)
Kind Regards,
Ashwin A.K

#12

Thank you all for your reply/solution

#13

hey bro.how can this be done through excel activities only? i am not well versed in linq. can you give solution using excel activities only?

#14

check the final solution for both rank and at_kt Sample.zip (2.4 KB)