Assistance With Trying To Find The Largest Discrepancy Between 2 Rows/1000+ Columns of Data

Occasional Contributor

How can I find and sort the largest/smallest discrepancy between the values in 2 cells?

 

For Example: Lets say Row C and Row D are prices and I want to find which column(s) have the largest/smallest discrepancies of the values within the cells?

6 Replies

Hi @RKD2313 

 

easiest solution: calculate the difference between column C and column D in a separate column (e.g. column E).

=D1-C1

And then sort the list in a descending order by column E

@Martin_Weiss The only other consideration I could offer is using ABS(D1-C1) so that negative or positive bias is treated with the same weight for discrepancy.  100-2 and 2-100 are the same discrepancy but wouldn't be sorted as such with one being 98 and the other being -98.  Using ABS would force both to be 98 and sorted accordingly.  

Let me be more specific. My fault that I wasnt in my initial post. 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 from my original example)

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.

Thanks for your reply as well. To give more specific insight I replied with more details.

@RKD2313 

 

Without a sample is a bit difficult to properly understand your needs.
Well, find attached one possible solution based in what I could understand.

 

 

 

=TRANSPOSE(FILTER(A1:K2,LAMBDA(amazon,ebay,ABS(amazon<ebay))(A2:K2,A3:K3)=1))

 

 

@RKD2313 I think I have it.  See attached.   

 

You just update the orange references (B1#,B2#,etc) below to the ranges in your workbook

=LET(
Parts,B1#,
Amazon,B2#,
Other,B3#,
NumResults,B6,
AbVarPer,((Amazon-ABS(Amazon-Other))/Amazon),
AbVar,ABS(Amazon-Other),
HSTACK({"Part";"Amazon";"Other";"Abs Var";"Abs %"},SORT(FILTER(VSTACK(Parts,Amazon,Other,AbVar,AbVarPer),AbVarPer<=SMALL(AbVarPer,NumResults)),5,1,TRUE))
)

 And, as you can see, simply type how many of the top variances you want to review to get those results.  

BA1F3641-D01F-435D-9F05-E889AFE3E817.GIF

This uses Abs Variance as a Percentage of Amazon Price.  An Absolute Variance of $10 is a big deal for a $25 product when compared to a $10 variance on a $125 product.    

 

Hope this solves your challenge.  Let me know of any questions.  

 

Dexter