Pull in top products but not duplicates

[samvince]

Hi,
I have a list of products and I need to pull into the table in the "Dashboard" sheet the top 25 products based on ROI and bottom 25 products based on ROI.

However, because there is sometimes more than 1 product that has the same ASIN sold at the same ROI it will pull in the same product multiple times.

So how do I pull in the top 25 Individual products based on ROI

Thanks

[RaulSerg]

You need to fix your file first.

It has circularities, e.g. Sheet Transaction Report column F.
Furthermore e.g. the same column has ref errors: =VLOOKUP(@F:F,#REF!,6,0)

Sorry, but no point taking a look at a messed up file. Garbage in, garbage out.
You can't trust results this way.

Regards

[samvince]

ok thanks for pointing out, updated the sheet. cheers

[oeldere]

With a pivot table for the top 25.

See the attached file.

[RaulSerg]

Attached for top values without pivot table.
btw - your database looks wrong to me. See the yellow cell.
This RoI refers to ASIN X07MNZV828 but in one instance the product is left blank and twice it says Product 10. Since the ASINs are the same, it looks like a database error.

You also need to be cautious. The above case reveals that in case different products/ASINs have the exactly same RoI, only the first occurrence/match gets populated.
That is, this formulas MAY fail in a very big data set where DIFFERENT products/ASINs have the exact same ROI (your database seems to only have SAME RoI for SAME).
The latter case would require somewhat more complicated formulae and/or helper columns. But let's cross that bridge when you get there^^

[Czeslaw]

Another option

[RaulSerg]

Another option

I think the result doesn't fulfill the requirement of no duplicates.
E.g. this should only appear once as Top #3:

3 X002XFX3XQ Product 1 107.31%
4 X002XFX3XQ Product 1 107.31%
5 X002XFX3XQ Product 1 107.31%
6 X002XFX3XQ Product 1 107.31%

[Bo_Ry]

Please try at
B8:D37

Please Login or Register  to view this content.

G8:I37

Please Login or Register  to view this content.