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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 30, 2022

CaitlynEXCEL 

It very depends on which Excel version/platform you are. As variant

=MID( D4,FIND("[", D4)+1, LEN(D4) - FIND("[", D4)-1  ) -
  SUMPRODUCT( ( LEFT( $A$4:$A$6, FIND(" ", $A$4:$A$6) ) = LEFT(D4, FIND(" ",D4) ) ) *
              ( MID( $A$4:$A$6,FIND("[", $A$4:$A$6)+1, LEN( $A$4:$A$6) - FIND("[", $A$4:$A$6)-1  ) ) )

but on Excel 365 it could be done much easier.

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