Offset (volatility)

[gregg141]

So here is what I have to work with, I've attached a sample of my solution to the post but I think the formulas are causing some problems to the file.

The blue part will be different for each different section (a typical file will have 400-500 different blue parts). Basically I'm trying to turn it from a horizontally sorted data table to a reduced vertical one (the real file has ~100 items in the blue part but at the most 10 of those items will be used per section).

So the green part is what I came up with, it uses indexes, matches and offsets to find the "used" items and returns their quantities as well.

I still think there may be a more simple way to go about doing this but I'm not too sure how (just would like a few opinions seeing as it adds a bunch of offsets to an already heavy excel file). I was thinking a macro solution would be possible but I don't know VBA, although it'd be a good opportunity to learn!

Thanks!

[martindwilson]

try like this

[gregg141]

Done, added it to the file, made it larger for some reason but the end result shaved off a bit of calculation time!

I think it's more of an issue of the file itself. Only 1 tab is really used but a bunch of info is fetched from various other tabs. Still everything I can optimize is precious!

Thanks