Need to run "excel for each row" based on column iterations

Help StudioX
, , ,
1

Hi

I am a beginner with StudioX and RPA. I am trying to figure out an activity to run an automation based on the below conditions.

A set of activities should run for each book in the table shown in the image. Some books might have single author or ISBN / multiple author or ISBN. They will be filled into an online form. How can I make sure, the automation runs for each book but only for its author(s) or ISBN(s)? I hope I am clear.

image

2

Sorry for the incorrect marking for book 1 entry in the original query. Book 1 has 3 ISBNs and Book 2 has 1 ISBN, as shown below.

image

3

I’m not sure if i understood , but doesn’t help if you limit the range of the “for each row” activity to A2:A8 or B2:B10 or C2:C8 insted of the entire range A2:C10? as i said i’m not sure if i understood corectly, but you can limit the range to the one you need to iterate over it.

4

I suppose your suggestion would work. Thanks for your help. I will work on it and get back to you if there is any concerns.

5

Hi s.pazhayattil. have you successfully with this? I have same problem but can’t solve. Can you tell how do you solve this?

6

If I understand correctly, the primary issues here are:

  • Sometimes the book is empty so you have to assume the last book value higher in the column.
  • Sometimes the author or ISBN is empty and you want to choose to use only one based on it being non-empty.

Given I don’t know the structure of the site you are entering the data into, this may change a little bit, but the high level way to approach this is inside an “Excel For Each Row” add two if statements:

  1. Check if the “Book” value of the current row is not empty, if it is not empty write that value into a temporary location in the project notebook (e.g. Scratchpad!A1). This is now your current book and anytime you need it, get the value from that cell in the project notebook (it will always contain the most recently seen non-empty book name value)
  2. Assuming author would take priority over ISBN, check if the “Author” is not empty (if you prefer ISBN to take priority check that instead) then inside the “Then” “Else” statements take the appropriate actions.

In the screenshot, I’ve assumed that either the Author Name or ISBN would go into the same field, so I’ve just stored them in a “value_to_enter” cell in my project notebook so I could use that value in a “Type Into” activity but if you put them in different places you’d want to put the actions directly in the “Then” and “Else” blocks.

image
image854×811 27.6 KB

7

Hi Andrew. Thank you for your quick reply.
In my case I have a spreadsheet in which I use to enter the data in the ERP. For each patient I will insert a EXAM. However, there are situations where I need to insert 2 or more EXAMS for the same patient and only then continue with the next patient. The NR_SEQUENCE column defines how many EXAMS the patient will have.

image
image791×328 11.8 KB

For example:

I make a flow using the IF inside “Excel For Each Row” and in WHEN I put another Excel For Each Row for a condition where NR_SEQUENCE greater than 1, but without success. Even if the condition is reached, the flow does not skip to the next PATIENT.

My flow is:

flow
flow1001×646 8.65 KB

Can you help me with this flow?

8

Am I understanding correctly that what matters here is the count in the NR_SEQUENCE row, meaning if it has more than one, you need to take the same action for that exam type, but in your sample spreadsheet above you’d take different actions for a different row (because it’s a different exam type) it doesn’t matter if the row above was the same patient?

If that’s correct, I think you can achieve this using a “Repeat Number of Times” activity and use the value from the NR_SEQUENCE as the “Repeat number of times value”.
image

9

"but in your sample spreadsheet above you’d take different actions for a different row (because it’s a different exam type) it doesn’t matter if the row above was the same patient?"*
You are right. My flow is not clear about that. Anyway, i have to take same action for a NEW EXAM, and than skip to next PATIENT.

10

Andrew, i write a movie with my full process. Look…uipath_flow.zip (1.9 MB)

11

OK, I think I understand

The easiest way to do this will probably require you to use the “Advanced” editor because you want to look at the value in the next cell to decide whether to finish the current patient or keep the screen open for writing an exam. What I would do is add a formula to store the next NR_SEQUENCE value in a temporary cell so you can use it in a condition (e.g. if the next NR_SEQUENCE is greater than 1 it’s the same patient, if it’s 1, it’s a new patient). Note: I’m assuming you know the column by address that contains either the patient or NR_SEQUENCE (you can really use either for this it will just change your condition, for my purposes I’ll just the NR_SEQUENCE) and the first row. For the purpose of this, I’ll assume that NR_SEQUENCE is in the E column and the data starts on Row 2

To do this:

  • Add a “Write Cell” activity
  • In the “What to write” entry, click the + choose “Indicate in Excel” for the Excel file containing the data and choose the first cell with a value for the NR_SEQUENCE (I’m using it E2 here but you can change as needed)
  • Click the + icon again and choose “Advanced Editor”, it will look something like this “Excel.Sheet(“Sheet1”).Cell(“E2”)”
  • Replace the “E2” with “E” + (2 + CurrentIndex).ToString() so the whole things reads like Excel.Sheet(“Sheet1”).Cell(“E” + (2 + CurrentIndex).ToString())
  • Note: in the above formula the 2 is the row your data starts on so if it started on row 14 instead of row two it should read (14 + CurrentIndex).ToString()
  • In the where to write, pick a temp location in your project notebook (e.g. Scratchpad!A1)
  • Then in your condition builder add a condition for the value in Scratchpad!A1.
13

Andrew works like a charm, BUT after finishing the flow, instead of reading the next PATIENT he is reading the next LINE. I don’t know what I’m doing wrong.

14

Sorry, I didn’t get a notification you had replied so just saw this. Did you get it figured out? If not, are you able to share a screenshot or zip and upload the flow (please don’t include any real data with it)?

15

Hi Andrew. I solve my problem. I use NR_SEQUENCE only one time, at the start of my flow. I use a condition like this:

start flow
if nr_sequence = 1 then go to new patient flow, else go to exam flow.