Gap Analysis / Incorrect results.

[Ceetrix]

Hi, I have this problem that has been kicking my *** for the last few days, and I need your help for a hot minute.

Context: I'm developing a model who's function is to reveal gaps in inventory i.e. gap analysis. The products are car parts, and whether or not we have a part in stock for a particular vehicle model.

Let's say the bosses wants to figure out what are the gaps among all vehicle models between the model-years 2010 and 2000, and only for vehicles with over 1,000 registrations. Under these conditions I will find that we have 17 products we do not cover and this is correct.

Problem: However, the issue is that in the event a car model has the same number of registrations, it will always show the first one on the list regardless if it breaks the conditions or not. What I do not understand is why the formula ignores the production year and stock conditions.

Some suggestions I've found on the net requires sorting or manual work in each case. This is not viable as the real data has 70,000 different vehicles to consider. Moreover, the whole point of this project is automation and removing the need for manual sorting.

I've included some real data and the formulas I've used to illustrate the problem, and hope some of you can give me some insight whether or not this is a solvable problem.

Kind regards,

Pål

[AliGW]

Welcome to the forum.

Windows 10 is NOT your MS Office version - please go back to your user profile and change this. Thanks.

[MrShorty]

That is how Excel's lookup formulas work when the "exact match" option is used -- if there are multiple copies of the same lookup value in the lookup range, then it will return the first entry it comes to. The solutions that I see involve doing something to make sure that each entry in the lookup range/array is unique. My ideas involve adding something (a text string or a number) to the end of each duplicate entry and then finding and searching for that -- almost always in a helper column (because I find things much easier that way). I'm not sure what limitations you have on this sheet or exactly how you might want to implement that, but that's what I see needing to happen.

Somewhat unrelated (though it might impact decisions made regarding the previous problem), exact match lookups like you have are notoriously slow. You indicate that this database will grow fairly large. I don't know the limits of your patience, but I would expect that, at some point as this grows, it is going to reach a point where it begins to test your patience. I notice that you are repeating the exact same slow linear lookup 5 times in each row. You might consider some of the things talked about in this post https://www.excelforum.com/excel-for...ml#post4041181 to eliminate that duplicated effort to speed up the spreadsheet.

[Ceetrix]

I fixed it, or kind-of. I implemented a search function and so they can quickly look up faulty values. This works well enough for our purposes since the error rate is not that large.

Moreover, I managed to reduce my processing time by an order of magnitude (from 40s to 4s) thanks to your tip! Appreciate it bud