Forum Discussion

RKD2313's avatar
RKD2313
Copper Contributor
Sep 21, 2022

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

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/smalle...
Formulas and Functions
Martin_Weiss's avatar
Martin_Weiss
Bronze Contributor
Sep 21, 2022

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

RKD2313's avatar
RKD2313
Copper Contributor
Sep 22, 2022

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.

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 23, 2022

    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.  

    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

Resources