How to extract excel data and form table?

Hi, I am a beginner of UiPath and I am asked to build a table based on the info. I have no clue on how to start with, can anyone provide some guidances for me? Due to privacy issue, I have created a excel file and made up some data.

I want to extract the best and the worst performed company, benchmark Q3 average return and the year-to-date return in each catagory according to their return. As Food - Local and Food - Chain store belong to the same catagory of “Food”, I need the robot to help me read through these two catagories and find the 5 figures.

The data is ranked according to Q3 average return from the highest to the lowest. For the food industry I just want to use the average return of “Food - Local” for ranking, ignore the average return of “Food-Chain store”.

The number of companies for other quaters may differ, how can I select the correct range and identify the highest and the lowest figures. So that the robot can still be used in the future.

Sheet 1 is the raw data, Sheet 2 is the result that I want. Is there any way to do so? Thanks in advance. :pray: :pray: :pray:

Q3 Ranking.xlsx (11.5 KB)

Hi @Katrina Welcome !

You could start looking here some post already with solutions to your request …

Like this one …

Try it and if something doesnt work as you expected you can ask again.

Thank you for your suggestion. But how can I extract each category one by one e.g. A9-D11, A40-D42…, then identify the highest and the lowest. The row index of other categories in other excel sheet differ, so I cant assign robot to read the specific range.

You can keep the read range in blank so with that you will be able to get all your data, then you can filter what you need or delete data that you don’t need.

One way to get the highest is sort descending and get the first element.

@Katrina bot can identify the correct range of each category using look up range activity. Please find below sample code which identifies the range of one of the category

Sample.zip (2.2 KB)

I understand what you mean, but I don’t know how to do it.

oh ok… in that case you should start taking the course first … Have you done any course in the Academy?

This is specific in your topic… https://academy.uipath.com/learningpath/datatables-and-excel-automation-with-studio

Thank you, I will take a look first.