1. ## Offsetting transfers

I have a large volume of data concerning transfers from and to various departments. There is a From Column and a To column plus an amount. What i'm attempting to do is create a Pivot table or other solution where the different rows offset when the From and Tos are the same, so if it's A to B for 3 and B to A on the next row is for 2, the net transaction is 1 between them. There could be C to D and D to C, as well as A to D and D to A.
I'm not sure how to change the sign on one of them so I don't get 5.
Any suggestions would be appreciated! TYIA

I don't know about doing it with a pivot table but you can do it with SumIfs formulas as follows:
=SUMIFS(\$C\$2:\$C\$13,\$A\$2:\$A\$13,\$E2,\$B\$2:\$B\$13,\$F2)-SUMIFS(\$C\$2:\$C\$13,\$A\$2:\$A\$13,\$F2,\$B\$2:\$B\$13,\$E2)
Using a setup like this:
Torq1.JPG
Note: The above formula goes in cell G2 and as written if fully able to be filled down for as many combinations as you need. Of course you could also move columns E-G to another sheet and just change the formula to include sheet references.

I'd also recommend replacing the Sum Range \$C\$2:\$C\$13 in the formula with a dynamic range name eliminating the need to ever change the cell references as you add data down the worksheet.

HTH

Test File: Torq1.xlsx

3. Many thanks. I didn't think of that approach, but have used SUMPRODUCT alot over the years. I'll work on it!

4. Actually, this isn't quite it. My example was probably off, and was missing a key ingredient.

The idea is that A ships to B 20 of product 1, then later B ships to A 6 of product 1. The net A<>B is 14 of product 1 between A and B. Or A to C and C to A. Or B to C and C to B, for products 1, 2, 3...n, and hundreds of As and Bs and Cs. So I'm trying to boil down the A<>B, A<>C, C<>B by product, with the kicker that the individual transaction records are all positive in quantity. It's to create a financial adjustment summarizing opertations system details. We don't need every transaction in the financials, just the net effect for a period.
Thanks again for the suggestion. I'm working on modifying it to do the above, but any ideas would be appreciated.

See if this is what you are trying to do. The Net Qty (col K) is the sum of all the transactions for the specific product based on the direction shipped.

Direction.png

Cell K2 =SUMIF(\$A\$2:\$A\$9,J2,\$E\$2:\$E\$9) and copy down which is adding the net values in a hidden column E for a specific product

Column E gets it values from the Qty shipped (col D) times 1 or -1 depending on the direction shipped

Direction2.png
Cell E2 =IF(B2<C2,D2,-1*D2) and copy down. So if A to B, Column E will be Positive but from Column B to A, Column E will be negative. Same with A<>C or B<>C.

When the positive and negative values in column E are added together for a specific product, a running total (Net Qty) can be kept for that product.

HTH,
Maud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•