Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Torquemada,

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks. I didn't think of that approach, but have used SUMPRODUCT alot over the years. I'll work on it!

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    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.

    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
    Attached Files Attached Files
    Last edited by Maudibe; 2015-02-20 at 21:44. Reason: added file

Posting Permissions

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