Offsetting transfers

Topic

New Reply

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

Reply | Quote

Replies

Torquemada,

I don’t know about doing it with a pivot table but you can do it with SumIfs formulas as follows:
[noparse]=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)[/noparse]
Using a setup like this:
39382-Torq1
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 [noparse]$C$2:$C$13[/noparse] 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 :cheers:

Test File: 39384-Torq1

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 AB 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 AB, AC, CB 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.

Reply | Quote

Torquemada,

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.

39493-Direction

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

39492-Direction2
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 AC or BC.

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

Reply | Quote