Forum Discussion
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?
7 Replies
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.
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
PeterBartholomew1 SergeiBaklan sivakumarrj CaitlynEXCEL
One of Excel's most redeeming features: there are so often multiple ways, different methods, to get from A to B. This thread illustrates that great truth!
- It seems that USER ID is suffix with quantity, better remove brackets and numbers, then YOU can do easily vlookup and index match for substraction
Without removal of quantity from user id just follow ,
A B D E F
KOCEB96(113) 123 KOCEB96(133) 241 118
KOCE7N9(117) 107 KOCE7N9(235) 235 128
=VLOOKUP(D2,$D$1:$E$8,2,0)-VLOOKUP(LEFT(D2,7)&"*",$A$1:$B$8,2,0) Let me correct what I wrote last night, but still with a caveat. It occurred to me overnight that there are lookup tools that allow for something other than an exact match, so it IS possible to lookup KOCEB96, leaving off the number in brackets at the end of the user ID.
HOWEVER, in order for that approximate match (which IS what it is) to work, the table(s) in which the search is being conducted needs to be sorted in alphanumeric order. If you sort the tables, then you can reliably search just by the first seven characters of the user IDs. It also turns out, however, that less than exact matches are problematic because Excel drops back one row after finding the closest match.
So in the attached spreadsheet I'm using a combination called INDEX and MATCH. MATCH finds a row number where the closest match to the first seven characters of the User ID are located, and then bounces back one; so I add 1 to that number and then use it in INDEX to return the value in the second column of your tables.
This is not beginner stuff. As I suggested in my earlier message, it would be cleaner, far cleaner, if the source of this data didn't blend the bracketed number in with the user ID. That muddies the water.
Here's a link where you can study up on the INDEX/MATCH combination
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?
Yes. A formula like
=VLOOKUP("KOCEB96[113]",A4:B14,2,0)-VLOOKUP("KOCEB96[235]",D4:E14,2,0)
would return the value -122.
The UserIDs however are not matching (although the first seven characters are), given that they each have different numbers in the square brackets at the end of each ID. It would be a lot cleaner (and easier for a beginner) if you did not have those.
Given that the numbers are embedded in the UserID (in the square brackets), you could also extract that value from the user ID string of text. But the VLOOKUP is the easier way.
Here's a good reference to understand VLOOKUP.
