Subtracting two columns if two other columns match

Question

I need to subtract the sum of ColumnD values that relate (same row) to each specific ColumnC value from the sum of ColumnB values relating to the same specific value when in ColumnA.

A         B          C          D      E       F
123       1          123        1      123     output (s/b -3)
123       1          345        2      345     output (s/b -4)
345       2          123        4      567     output (s/b  0)
345       3          567        5
567       5          345        7

In this example I would need the 123's to show -3, 345's to show -4, 567's to show 0. I would then sort and in ColumnE have only the unique number sets with the output showing in ColumnF. I'm open to other suggestions as well! I have been playing around with this for a while and haven't been able to figure it out.

Answer 1

Please try in F2 and copied down:

=SUMIF(A:A,E2,B:B)-SUMIF(C:C,E2,D:D)

Answer 2

Give this a try:

Sub Adjuster()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To N
        If Cells(i, 1).Value = Cells(i, 3).Value Then
            Cells(i, 4).Value = Cells(i, 4).Value - Cells(i, 2).Value
        End If
    Next i
End Sub

EDIT#1

This approach uses Macros

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!