Comparing a Price to other Prices and taking the lower Prices

[Zaramot]

Hello. So i made an Automation to Search for Product Numbers (Source: Product.xlsx) on Amazon and Scrape the Price, Name and URL and Create for each Product Number an Excel and save the Scrape Informations into them.

Now i want to Compare the avergage Price (which is in the Workbook “Products”) to the Prices i scraped and save them with Price and URL into a Value (so i can send them later with an Email).

For example:
Product Number UC3541A average Price is 103 and i want all the lower Prices from the excel i created for the specific Product Number

Any Ideas how i should do that in a For Loop ofc. So i can do it for all Product Numbers?

Attached the 2 Excels and my .xaml

Main_new.xaml (69.6 KB)
Products.xlsx (15.6 KB)
UC3541A 24_07_2023.xlsx (10.4 KB)

[Anil_G (Anil Gorthi)]

@Zaramot

1. Create a loop on your average price list excel data say dt1
2. and say the data for all products scrapped is in dt2
   3.inside loop use dt2.AsEnumerable.Where(function(x) currentRow("ProductName").ToString.Split({" "},StringSplitOptions.None).All(function(y) x("ProductName").ToString.ToLower.Contains(y.ToLower))).Count>0 to first find if atleast one row is matching using if condition
3. on then side you can get the sum or average of the filtered rows…the above expression without count will give the set of rows which match the product name

cheers