Compare a value in a col to a value in another col in diff session and perform actns

[Tonygets]

Hi,

I am using AA 5.5.1 Enterprise.

I am trying to create a step that allows me to compare a cell in a Column in session: ‘MSO’ of an excel spreadsheet to another column in session: ‘Master’ of another excel spreadsheet, and then performs an action if a match is not found.

I would appreciate help with 2 steps:

1) I have got multiple cells from A2 to AB100 in session: ‘MSO’. Is there a way to ‘get multiple cells’ for specified columns but for rows that only have values? (The spreadsheet can usually contain 500+ rows).

2) I want to now run an IF formula to see if the value in ExcelColumn(3) (OrderNumber) in session: ‘MSO’ is EQUAL to a value in ANY row of Column E (OrderNumber) of session: ‘Master’ (Rows in session: ‘Master’ will constantly vary as the file grows).

If the value in ExcelColumn(3) (OrderNumber) session: ‘MSO’ equals a value in a row of Column E (OrderNumber) session: ‘Master’ then I want to move to the next row and not perform any actions. If the value does not equal a value in session: ‘Master’ then I want to perform the next action (which will be to copy data in that row across from MSO to Master).

I have some code already written however when I run it just loops and does not move to the next action. I have attached my code and the sample files. Can you please modify it to meet my requirements above?

Thanks and regards,

Tony

[forumsupport]

Hello,

Regarding your queries,

1) To get rows with values, you can put 'If Variable' condition inside 'Loop' command to check if particular column is not NULL then perform the actions in 'If-Endif' block.

2) You can use nested Loop for this. We have attached the amended task for your reference. If you could send the sample MSO_Orders_Sample.xls file then we can run the task at our end. To run attached task, please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.

Hope that helps.

[Tonygets]

Hi,

Thank you for this file.

I think there is an issue with the steps because the file just seems to get caught in a loop at step 17 or 18.

What the automation is doing is checking to see if a row from the latest sales orders is already contained in the master order file.

If the row is already contained in the master then it moves on to the next row of the orders and if the row is not contained then it copies that row into the first free row of the master spreadsheet.

There are multiple variables to identify if the orders row should or should not be pasted into the master spreadsheet.

The variables to REJECT a row in the orders spreadsheet and move to the next are:
1) if the OrderNumber matches from the active row in Column C (OrderNumber) in the orders spreadsheet to column E (OrderNumber) in the master spreadsheet.
2) if the text does not match 'Order placement successful' in Column L (OrderStatus) in the orders spreadsheet.
3) if there is data in column C (PurchaseOrder) in the master spreadsheet i.e. the cell is not 'blank'.

If the above conditions are not met then the row can be pasted from the orders spreadsheet into the master spreadsheet in the first available row.

I hope this helps clarify.

Can you please take another look at the automation and see if there were any errors?

Thanks and regards.

[forumsupport]

Hello,

Can you please upload the MSO_Orders_Sample.xls so we can modify the task accordingly? The .htm file for MSO_Orders_Sample.xls that you had uploaded shows Order Number is in B Column and as per your description it is the C column, also fields after Column H aren't visible hence the error.

Kindly upload the actual excel file so we can test the task at our end.

[Tonygets]

Hi,

I have attached updated sample documents. I apologise if the first samples were incomplete.

Please let me know if you have trouble with these docs?

Kind regards.

[forumsupport]

Hello,

Attached is the amended task. It works on three reject criteria you mentioned in previous post. Please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder and run it.

If you face difficulties with the task then please email us at Automation Anywhere - Open A Support Case and one of our support people will get in touch to resolve the issue quickly.

Hope that helps.

[Tonygets]

Thank you very much for this file. It is working well.

I need the same file for another automation however I need it to include 1 more condition. I thought I would be able to add the condition by following your work however I am not able to get it to work.

The condition to add to the list of previous conditions is that the specific row must only copy from the orders to the master if there is a value present in Column F (Vendor) in the orders spreadsheet for that row.

Also, is it possible to safely speed up the automation when it loops through each row in the master spreadsheet? This spreadsheet will contain hundreds of rows and it will take a very long time to run as the file grows.

Thanks and regards.

[forumsupport]

Hello,

We have amended the task to add one more condition as you mentioned. Please copy attached task under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder and run it.

Hope that helps.

[Tonygets]

Hi,

Thank you for the automation. The automation however does not work as required.

The point of the additional condition was to ensure the automation only wrote the row that contained product information in the orders spreadsheet to the master (the correct row(s) can be identified because they are the only row(s) within the order with a value in Column F (Vendor).

However in the automation file you posted this condition does not seem to be followed. (The other conditions are working fine however).

The error in this condition can be seen by the following explanation:

The OrderNumber in (column C) of the orders spreadsheet contains a variety of rows (product information, GST information and shipping information).

After all other conditions have been met, I do not want to write the rows which contain text 'gst' and 'shipping'.

The rows that contain a value in Column F (Vendor) are the actual rows that I want to write to the master. (I am using the value in Column F (Vendor) to identify the correct rows to write because the rows containing the 'gst' and 'shipping' text contain blank values in Column F (vendor)).

I have also attached a screenshot to further demonstrate this point.

Please can you amend and repost?

Thanks and regards.

[forumsupport]

Hello,

Attached is amended task as per the description.

Hope that helps.