Loop Through Two Tables and stop when condition is met

Hi everyone,

I have the following two tables:

TableSell = table([127;49;25;52;68;40;39],[1;2;3;4;5;6;7],[-1.27;-0.98;-0.75;-2.08;-3.4;-2.4;-2.37],'VariableNames', {'RandHouseLoad','PriceToPay','SellPrice'});
TableSell = sortrows(TableSell,{'PriceToPay'},{'ascend'});
TableSell.CumSumSell = cumsum(TableSell.RandHouseLoad)

TableBuy = table([-166;-32;-38;-165;-144;-31;-59],[14;13;13;12;11;11;10],[23.24;4.16;4.94;19.8;15.84;3.41;5.9],'VariableNames',{'RandHouseLoad','PriceToPay','BuyPrice'});
TableBuy = sortrows(TableBuy,{'PriceToPay'},{'descend'});
TableBuy.CumSumBuy = -1.* cumsum(TableBuy.RandHouseLoad)

I want to loop through the CumSumBuy column up until the CumSumBuy column is equal to or smaller than the max CumSumSell value, and then display the value of PriceToPay at that Specific row in TableBuy. (This is the case when the max CumSumSell value is smaller than the may CumSumBuy value).

If the max CumSumSell value would be larger than the max CumSumBuy value I want to display the PriceToBay value in the TableBuy for the max CumSumBuy value.

(In the example table above, TableSell and TableBuy have the same number of rows. This will not be always the case)

I hope this description makes sense to you.

Thanks a lot!

Answer 1

[Adam Danz]

Here's my interpretation of your sentences.

% I want to loop through the CumSumBuy column up until the CumSumBuy column is equal 
% to or smaller than the max CumSumSell value, ...
% No need for loops; you just need to find the first row of CumSumBuy that is <= max(CumSumSell).
rowIdx = find(TableBuy.CumSumBuy <= max(TableSell.CumSumSell),1); 

% ...and then display (in command window?) the value of PriceToPay at that Specific row in TableBuy.
fprintf('Price to pay: $%.2f\n', TableBuy.PriceToPay(rowIdx))

% If the max CumSumSell value would be larger than the max CumSumBuy value 
% I want to display the PriceToBay value in the TableBuy for the max CumSumBuy value.
if max(TableSell.CumSumSell) > max(TableBuy.CumSumBuy)
    [~, maxBuyIdx] = max(TableBuy.CumSumBuy);
    fprintf('Price to buy: $%.2f\n', TableBuy.PriceToPay(maxBuyIdx))
end

This assumes the rows of TableBuy are associated with the rows of TableSell but you mentioned those rows might not be equal which would dissociate the rows between the two tables.