Forum Discussion

CaitlynEXCEL's avatar
CaitlynEXCEL
Copper Contributor
Jul 30, 2022

How to subtract from the same user

I know I can just do something like =E4-B6 but is there a way for it to look up by the User ID and subtract the quantities with matching Users?    
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 30, 2022

Note: This solution is limited by the requirement that each user ID should occur the same number of times in each table and it requires a very up-to-date version of 365.

 

WorksheetFormula
= LET(
  stacked,   VSTACK(table1,table2),
  sortedVal, DROP(SORT(stacked),,1),
  paired,    WRAPROWS(sortedVal,2),
  BYROW(paired, Diffλ));

Diffλ
= LAMBDA(pair, SUM({-1,1}*pair));

 

It first stacks the two tables;

then it sorts on User ID but only retains the associated values as a single column;

next it then uses WRAPROWS to create the two columns of corresponding values;

finally it differences the two columns using a Lambda function. 

In this instance I returned the IDs with a distinct formula

 

= SORT(TEXTBEFORE(TAKE(table1,,1), "["))

 

Sorry Sergei, this wasn't specifically addressed to you. I simply used your workbook as the starting point

Resources