I have a 4016 x 4 cell, called 'totalSalesCell'. The first two columns contain text the remaining two are numeric.
1st field CompanyName
2nd field UniqueID
3rd field NumberItems
4th field TotalValue
In my code I have a loop which goes over the last month in weekly steps - i.e. 4 loops.
At each loop my code returns a cell of the same structure as totalSalesCell, called weeklySalesCell which generally contains a different number of rows to totalSalesCell.
There are two things I need to do. First if weeklySalesCell contains a company that is not in totalSalesCell it needs to be added to totalSalesCell, which I believe the code below will do for me.
co_list = unique([totalSalesCell(:, 1); weeklySalesCell (:, 1)]);
index = ismember(co_list, totalSalesCell(:, 1));
new_co = co_list(index==0, :);
totalSalesCell = [totalSalesCell; new_co];
The second thing I need to do and am unsure of the best way of going about it is to then add the weeklySalesCell numeric fields to the totalSalesCell. As mentioned the cells will 90% of the time have different row numbers so cannot apply a simple addition. Below is an example of what I wish to achieve.
totalSalesCell weeklySalesCell Result
co_id sales_value co_id sales_value co_id sales_value
23DFG 5 DGH84 3 23DFG 5
DGH84 6 ABC33 1 DGH84 9
12345 7 PLM78 4 ABC33 1
PLM78 4 12345 3 12345 10
KLH11 11 PLM78 8
KLH11 11
I believe the following codes must take care of both of your tasks -
[x1,x2] = ismember(totalSalesCell(:,1),weeklySalesCell(:,1))
corr_c2 = nonzeros(x1.*x2)
newval = cell2mat(totalSalesCell(x1,2)) + cell2mat(weeklySalesCell(corr_c2,2))
totalSalesCell(x1,2) = num2cell(newval)
excl_c2 = ~ismember(weeklySalesCell(:,1),totalSalesCell(:,1))
out = vertcat(totalSalesCell,weeklySalesCell(excl_c2,:)) %// desired output
Output -
out =
'23DFG' [ 5]
'DGH8444' [ 9]
'12345' [10]
'PLM78' [ 8]
'KLH11' [11]
'ABC33' [ 1]