Select Cells in Excel Based on Cell Value

Help
1

Hello - I am pulling values from an excel sheet, for example, I have six columns that are all named “Total”. I am pulling these 6 columns using Read Range, with range selection of
“E1:E100,H1:H100, K1:K100,N1:N100,Q1:Q100,T1:T100”

Now, how can I write a variable that selects all the values from this range with value of “0”?

For this I have created a variable called Array and data type of DataRow. I want to set this variable to equal all the rows where value is “0”, no matter what column they are in, in above range.

Please let me know if you need additional details or explanation. Thanks!

2

ITwo questions

  1. will the range you specified works in Uipath read range activity? If so is it fetching only 6 columns or all columns between E and T?
  2. Is all the 6 column names Total? If so do not check Add Header

Assuming 1 works, build a data table with 6 columns and add the range to the data table and you can do
Dt.Select(“col1=0 or col2=0 …or col6=0”) and assign it to your array.

3

Hello - First, yes, all 6 columns are named “Total”. I’ve adjusted my Read Range to select everything in the sheet “” and excluded the headers from DataTable. Next I assigned:

Table.Select(“Col5=0 or Col8=0 or Col11=0 or Col4=0 or Col7=0 or Col20=0”) to my array

But, I’m getting an error saying “Cannot find Column [Col5]”

4

Where did you specify col5 col8…?

5

Default column names are Column-n, try those instead. Remember its 0-based as well.

6

Right after Read Range, see image below.

image

7

Hello Andrzej, could you please elaborate? Like below:

Table.Select(“Column5=0 or Column8=0 …)

Thanks!

8

Besides @andrzej.kniola solution, if you are getting 6 columns why are you specifying column5 column8 instead of column0 column1 …column5

9

Hard to Type it on mobile… :stuck_out_tongue:

Since its 0-based, 5th column is Column-4 etc. Brackets are to make sure interpreter correctly sees it as column name.
If it doesnt work, use output data table activity to see how it looks in message box.

10

I’m here for @andrzej.kniola .
People use their mobile for Twitter/FB .
Hats-off pal.:rofl::+1:

1 Like
11

Hi, I am new to UiPath.

I need help to sort out and arrange list of items with date and time into different boxes.

Example: Items from time range 01:00 to 03:00 into box 1 and items from time range 03:01 to 05:00 into box 2.

Items list and Box list are from different excel file.

Appreciate if anyone can help, thanks.

Items%20with%20DateTime

Boxes%20to%20contain%20items%20within%20time%20range
Boxes%20to%20contain%20items%20within%20time%20range1161×210 30.6 KB

12

@ENC

Read the excel sheet and store it in a datatable

Then extract time part and check for the range
Based on that add them to different collections
At Last add the values to other excel sheet in the required format.

Regards,
Mahesh

13

Hi @MAHESH1,

Thank you and appreciate for your help.

If possible, able to advise on how to check for the range?

Thanks
Regards,
ENC

14

@ENC
Create the lists like List A, List B …
1.Run one foreach row for the excel sheet which you have read and stored in a datatable
2.then extract the time part of second column and replace the colon by dot(: by .)
3.then check whether the value extracted is less than 3.00 if it so add that to List A
4.Else check is it less than 6.00 if it is then add that to list B and so on
5.Finally after completion enter all the values in different lists to different columns.

Regards,
Mahesh

15

Hi @MAHESH1,

Thank you for clarifying.

Lastly, do I need to convert column “B” to datetime if the date changes daily?

Usually the column “B” starts from current day 20:00 till next day 15:00.

Thanks
Regards,
ENC

16

@ENC

If your excel format remains same with date and time no need to convert to datetime format,
You can extract the time part by using split function.

And if you just want to compare the time itself then no need to take care about date .

Regards,
Mahesh

1 Like
17

Hi @MAHESH1,

Thank you for your help and clarification.

:slight_smile:

Thanks
Regards,
ENC