Combine data in columns (A-Current portfolio, B-buys, C-sells, Result-resulting portfolio)

[mackti]

Hi all,

1. I have a series of current investments in columns (fields = InvestmentName, Units, Value).
2. I then have a series of newly acquired investments in columns (fields = InvestmentName, Units, Value). These may be additions to the current investments or new investments.
3. I then have a series of sold investments in columns (fields = InvestmentName, Units, Value).they have to be in the current investments (you can’t sell what you don’t have nor can you sell more than you have).

My question is this – is there an easy way of doing this in Excel VBA?
I can (I actually have) written code) that loops through every investment in A, checks if any existing investment in B needs to be added to it and then checks if anything from C needs to be subtracted from A (via lookup). It then adds this to the output.
Then the code goes through any new investment in B (that isn’t in A) and adds it to the output as a new investment.
It somehow works but it seems convoluted. Is there an easier way to combine these three data sets? (A + B – C) that show the output? (with anything from A that is 100% sold isn’t shown in the results).
I’ve attached an example of the problem and below it the manual solution (well I created an example XLS & was going to attach it but system says I don't have that permission so I can't).
Any help ideas would be much appreciated.
If the way I’m doing it is the best way, then so be it. I’m also keen to learn smarter ways of doing these things (can also cut code looping time I imagine).
Cheers
Tim

 

[Fazza]

hi, Tim

Maybe use a pivot table. It can combine data from multiple tables and not show data that nets to zero quantity.
Or very similar using a query table. Neither needs code but you could have a single code line for refreshing.

regards, Fazza

 

[mackti]

hi, Tim

Maybe use a pivot table. It can combine data from multiple tables and not show data that nets to zero quantity.
Or very similar using a query table. Neither needs code but you could have a single code line for refreshing.

regards, Fazza

Thanks for the suggestion Fazza. After 25+ yrs of successfully avoiding understanding exactly what a pivot table was in Excel, maybe I need to turn my mind to it. I've always just turned to MS Access, vlookups,hlookups,indirect, nested ifs, filters, VBA code etc as solutions Cheers, Tim

 

[Fazza]

Being unfamiliar with pivot tables, MS Access might be a good tool.

Though VBA within Excel might be even better - cause you don't need two different softwares. It probably is simple enough in VBA that it would be better to do that than learn pivot tables. It is not a simple pivot table (though not too bad if you're famiilar with SQL).