Search if a substring from a list exists in a column and retrieval of values

Help Studio
, ,
1

Hi. I am new to uipath and I am trying to create a bot that will:
1)Check if a customer ID from sheet “Customers” exists in column A of sheet “sheet1”
2)If the customer ID exists it should write a line in sheet “TableGeneration” with the all the corresponding charges (some customers have more than one instance of charges from “sheet1”
3)Create a column in “TableGeneration” and fill it by noting if the customer is AASD or non-AASD.
4)Check if there is a charge for a customer which was not in the “customers” sheet
5)write a line with the name of the customer that was not found, the charges and the term “not in database” in column “is AASD”
I have been trying to do this for almost a week and even though I tried to find similar topics my search ended with results I could not comprehend due to my lack of experience.
I hope I have described the problem sufficiently.
Any help or guidance would be greatly appreciated, if possible with an xaml file.
Analysis.xlsx (15.9 KB)

2

Hi @geoiak !
Welcome to UiPath Community :grinning_face_with_smiling_eyes:
To help you we need more information:

  1. If I understand well, from “Customers” sheet we need to check if the ID is here whatever might be the line, just at A column (so it can be found at A2, to not be found or to be found several times like in A2, A5 etc)
  2. So if I find for instance the ID at the coordinate A3, then charges are at B3 ? Or should I sum up all the charges that are in the sheet with several instances? (so sum from B1 + B6 + etc?)
  3. Crystal clear
  4. If we recall (1) we work from customers sheet to sheet1, and now we do the inverse work (from sheet1 to customers sheet) is it ?
  5. Crystal clear
3

@geoiak

Welcome to forums

For Searching the value exists in column, check below for your reference

Reference - 1

Reference - 2

Reference - 3

Hope this may help you

Thanks

4

Dear Hiba_b thank you and it is nice to be here!

  1. In “customers” sheet each customer has a unique Customer ID. However, in sheet 1 the same customer can have multiple charges.
    2)Exactly. Based on 1) after all instances have been found, the sum of all charges for the specific ID should be written in sheet table generation.
    3)-
    4 and 5) Actually 4 and 5 are together and maybe it could be clearer this way. If after 1),2) there are charges that do not correspond to a customer with a customer ID in sheet “Customers” then a line (with Customer, charges and AASD - Non-AASD columns) should be written in sheet “TableGeneration” below the rest of the lines with a value of “not in database” in AASD - Non-AASD column.
    I hope this helps you and thank you for taking the time!
5

Dear ksrinu070184, thank you very much.
I will check them out

6

Alright, thank you for the precisions. I am preparing a code, meanwhile I have a question: about the ID, could you give us more information about its form ?
I can see two forms:

  • starts with A + 10 numbers
  • is a number of 9 digits

Is there another style of ID that we should know about ?

7

@Hiba_B Thank you for the reply. It is rather random as the sources are multiple and kind of inconsistent

8

Well this might be a problem… the robot can’t guess in the cell what is considered being the ID :confused: There is no problem from customer sheet to sheet1, but there will be a problem from sheet1 of customer. Do you have an idea ?

9

Dear @Hiba_B, I managed to solve 1 by using a nested for each row and an “if” activity with “contains” operator to check if “description” column contains a value from “Service ID” column. it seems to work fine. I write a new column (check) with “ok” next to each row. I plan to write something distinct like “service ID” and then try to create the list based on getting every row with the specific service ID in the “check” column. I will let you know of the results.
Thank you!

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.