Identifying missing item in excel

Help Activities
, ,
1

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!

2

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

2 Likes
3

Hi, may I ask how do I sum up items in excel using uipath and auto fill into Google Sheet?

4

Hi @H_YL
can u give a idea on the process which u are thinking to automate?

5

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?

6

Hi @H_YL

You can try this way

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

  2. Use merge datatable activitiy to merge all the three datatable to one datatable let’s say dt4

  3. 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 :robot:

Happy Automation :relaxed::relaxed:

1 Like
7

Really thank you so much @NIVED_NAMBIAR! :blush::blush::blush:

1 Like
8

I am having error when i am putting the dt1.AsEnumerable(Function(row)row(columnname).ToString).Count=5 into the condition for IF

image
image1077Ă—292 14.4 KB

Can you please help me?

9

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 :robot:

Happy Automation :relaxed:

10

Noted. Thank you very much for your prompt reply!

1 Like
11

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?

image
image917Ă—228 14 KB

12

row(“item”) means u are calling a column value under item column for current row

13

So i just have to put columnname and it will work? but when i put it
there is an error as well

14

columnname should be within string

15

There is still error, am i doing something wrong?

image
image1330Ă—392 18.6 KB

image
image1506Ă—595 41.1 KB

16

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

17

Hi @NIVED_NAMBIAR may I ask you a question what does Cstr mean? And Cint mean in UI path? And why To.string mean?

18

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 :robot:

Happy Automation :relaxed:

1 Like
21

Thank you @NIVED_NAMBIAR for your explanation! :blush:

1 Like