Question Excel/Google Sheets Formula/Lookup Method To Find Largest Discrepancy

[RKD2313]

The basis of this request is the fact that Im product hunting and looking for a more efficient way to source/scan data.

I have 1000+ products (Columns)
Each product has its price from Amazon & eBay (Rows C & D )

In most instances the prices are very similar. Not enough of a margin to make a profit. However, there are products where its significantly cheaper on 1 marketplace vs the other. Instead of scanning Column by Column it would be nice if there was a formula or some type of lookup method so that the prices for each product is collected and compared between Amazon & eBay. Maybe discrepancy is the wrong wording? I basically just want to filter/sort the results so that I can see the biggest difference between prices for any 1 product. That would help save an enormous amount of time. I would hypothetically only have to look at around 50-150 Products vs scanning over each product individually 1000+ times over.

 

[USAFRet]

Your data is structured wrong.

Products in Rows, Prices in cols.

Col A = Product
Col B = Amazon price
Col C = Ebay price
Col D = Col B - Col C

Sort by Col D, ascending.

 

[RKD2313]

Your data is structured wrong.

Products in Rows, Prices in cols.

Col A = Product
Col B = Amazon price
Col C = Ebay price
Col D = Col B - Col C

Sort by Col D, ascending.

Thank you for the easy clarification.

 

[Math Geek]

Your data is structured wrong.

Products in Rows, Prices in cols.

Col A = Product
Col B = Amazon price
Col C = Ebay price
Col D = Col B - Col C

Sort by Col D, ascending.

that's exactly what i was thinking as well. simply subtract the 2 prices and sort that column by size.

do note though that you don't want to be bothered by +/- results. since you only care about the difference and not which site has which price. so use the absolute value function in the calculation so it does not bother you with pos or neg values. this will save time when sorting so all the large differences will be at the top of the list.

 

[USAFRet]

that's exactly what i was thinking as well. simply subtract the 2 prices and sort that column by size.

do note though that you don't want to be bothered by +/- results. since you only care about the difference and not which site has which price. so use the absolute value function in the calculation so it does not bother you with pos or neg values. this will save time when sorting so all the large differences will be at the top of the list.

Yes, ABS is warranted.

Some items, Amazon may be cheaper. Others, ebay.
Absolute value.

 

[Ralston18]

And I will add the suggestion to apply conditional formatting to color code which price Amazon (Col B) or Ebay (Col C) is lower.