Forum Discussion
Assistance With Trying To Find The Largest Discrepancy Between 2 Rows/1000+ Columns of Data
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.
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