Grouping data with special conditions

[Rhys18 (Rizky Albert Hartono)]

Hi,

I have a data with special conditions like this :

The condition is :

1. Auction Company : Get Auction company from the HIGHEST sold price (1000 and 4500)

2. History Min Price, Auction Date History, and History Sold Price : Get the value based on date of 3 latest auction, (example in Honda, there’s 3 2022’s and 1 2021, so get the latest is 2022).

3. How the data write is starting from History Auction 3, then 2, then 1 (see picture, in the picture the case is mercedes and Hyundai)

4. If there is a 2nd 3rd or 4th auction price, the column ‘Minimum Price’ is ignored and we get between 2nd-4th auction price (see picture)

How do I get the expected output?

This is the excel File :

Expected.xlsx (9.5 KB)

[Felix_Effendi]

Hi @Rhys18 ,
will the expected output only show the last 3 auctions??

[Rhys18 (Rizky Albert Hartono)]

Yes. It’s only 3 history.

[Felix_Effendi]

ok i’ll try

[Felix_Effendi]

Hi @Rhys18 ,
Please take a look.
Sequence.xaml (24.8 KB)
Expected.xlsx (9.2 KB)

[Felix_Effendi]

Hi @Rhys18 , how is it? Is my solution works?

[Rhys18 (Rizky Albert Hartono)]

Good afternoon Mr. Felix,

I am currently in my office and right now working on my project, so I thought I would reply to you at night.

Yes it’s working !! I did some little modification on your code (about 10-20%) and it’s working as expected… I got question too.

When I use read range (classic activity) and excel process scope (UiPath.Excel.Activities) the result are different.

When I use classic activity, it’s working as expected.
When I use excel.actiivities, the date and the price are not as expected.

How could this small thing giving impact very big lol…

and second I forgot to add 1 more column that are needed… So I am currently trying right now, and if I’m on dead end, I will need your help again