Hi, so basically im wondering how i would go about using an activity that would identify if my excel is missing one or more items from a column. im looking at only one column in multiple sheets that are filled with integers. Usually, it will have 5 rows of numbers, but if there were to be 4 instead, i would like to have a message box that tells me so after identifying that one is missing. Thanks!
Hi @oxslate
you can read each sheet of excel using read range and then store in dt1
then use
dt1.AsEnumerable().Where(Function(row)row(columnname).ToString).Count=5 as condition in if activity to check if the values in column are equal to 5 or not, if the condition is true, then the column had 5 rows, else, not equal to 5 rows
Hope it helps
Regards,
Nived N
Happy Automation
Hi, may I ask how do I sum up items in excel using uipath and auto fill into Google Sheet?
Thank you @NIVED_NAMBIAR !
I have 3 excel sheets in the same workbook. With the same item but different numbers.
All the 3 sheets have two column and heading cell  A1 â€śItemâ€ť, B1 â€śNumberâ€ť.
Sheet 1 (A1:A6) â€śNameâ€ť â€śCokeâ€ť, â€śbreadâ€ť, â€śSausageâ€ť, â€śBiscuitâ€ť, â€śAppleâ€ť (B1:B6) â€śNumberâ€ť â€ś2â€ť, â€ś8â€ť, â€ś8â€ť, â€ś3â€ť, â€ś6â€ť.
Sheet 2 (A1:A6) same item as Sheet 1 (B1:B6) â€śNumberâ€ť, â€ś6â€ť, â€ś0â€ť, â€ś5â€ť, â€ś5â€ť, â€ś5â€ť.
Sheet 3 (A1:A6) same item as sheet 1 (B1:B6) â€śNumberâ€ť,
â€ś0â€ť, â€ś3â€ť, â€ś4â€ť, â€ś7â€ť, â€ś7â€ť.
I have to calculate the total of each item in the 3 execl sheet and auto fill into google form using UI path.
Q1.How many bottle of coke?
Q2. How many slices of bread?
Q3. How many sausages?
Q4. How many pieces of biscuits?
Q5. How many apples?
Hi @H_YL
You can try this way

Read the three sheet using read range seperately and then store in dt1,dt2,dt3 seperately.

Use merge datatable activitiy to merge all the three datatable to one datatable letâ€™s say dt4

Then use the sum linq way to get the sum of particular item using assign activities
Coke_sum = dt4.AsEnumerable().Where (Function(row) row(â€śItemâ€ť).ToString.Equals(â€śCokeâ€ť)).Sum(Function(row) CInt(row(â€śNumberâ€ť).ToString)).ToString
Where Coke_sum is a datatype of string.
Likewise for other items , we had to do in sa e way for assign activitiy.
bread_sum = dt4.AsEnumerable().Where (Function(row) row(â€śItemâ€ť).ToString.Equals(â€śbreadâ€ť)).Sum(Function(row) CInt(row(â€śNumberâ€ť).ToString)).ToString
sausages_sum = dt4.AsEnumerable().Where (Function(row) row(â€śItemâ€ť).ToString.Equals(â€śSausageâ€ť)).Sum(Function(row) CInt(row(â€śNumberâ€ť).ToString)).ToString
biscuit_sum = dt4.AsEnumerable().Where (Function(row) row(â€śItemâ€ť).ToString.Equals(â€śBiscuitâ€ť)).Sum(Function(row) CInt(row(â€śNumberâ€ť).ToString)).ToString
apple_sum = dt4.AsEnumerable().Where (Function(row) row(â€śItemâ€ť).ToString.Equals(â€śAppleâ€ť)).Sum(Function(row) CInt(row(â€śNumberâ€ť).ToString)).ToString
By this you will get sum of each item which u can enter to Google sheets
Regards
Nived N
Happy Automation
I am having error when i am putting the dt1.AsEnumerable(Function(row)row(columnname).ToString).Count=5 into the condition for IF
Can you please help me?
There is small correction
I had corrected in my previous response
Thanks for pointing that out
Put this one in if condition
dt1.AsEnumerable().Where(Function(row)row(columnname).ToString).Count=5
Regards
Nived N
Happy Automation
Noted. Thank you very much for your prompt reply!
I am having this trouble whether i put colomnname or other, it is of the same result.
Do i need to create a variables for the Item?
row(â€śitemâ€ť) means u are calling a column value under item column for current row
So i just have to put columnname and it will work? but when i put it
there is an error as well
columnname should be within string
Hi @jiacai.21
I will give u an example
letâ€™ say u had a column item in dt1,
now u need to apply linq on that column , so see below condition u can apply
dt1.AsEnumerable().Where(Function(row)row(â€śitemâ€ť).ToString).Count=5
Regards,
Nived N
Happy Automation
Hi @NIVED_NAMBIAR may I ask you a question what does Cstr mean? And Cint mean in UI path? And why To.string mean?
Hi @H_YL
Cstr is a function to convert the data to string.
CInt is used for converting a string to integer format
For eg, letâ€™s say there is string varaible having value â€ś3â€ť,
Using Cint(Stringvar), it convert the value to integer.
ToString do the same function as CStr
Hope the above above info helps you
Regards
Nived N
Happy Automation