Share via

Trying to match amounts

Anonymous
2012-02-18T18:02:46+00:00

In excel I am trying to look for items out of balance on a column.  I should have an exact debit to match my exact credit.  Is there a way to run a formula to pull out all debits which have equal offesetting credits so that all is left are those items with no corresponding items?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-02-19T10:22:10+00:00

    > ... items out of balance in a column ..

    Here's a formulas play which should do the job ...

    Assume your source values are running in A2 down

    (ie All figs are in col A only, scrambled and there may be mulltiple duplicate pairs of knock-off figs)

    In B2: =IF(A2="","",IF(A2<0,-A2&"_"&COUNTIF(A$2:A2,A2),A2&"_"&COUNTIF(A$2:A2,A2)))

    In C2: =IF(COUNTIF(B:B,B2)=2,"x","")

    Copy B2:C2 down to the last row of source data

    Col C will flag all knock-off items as "x"

    Filter on col C for "x" to retrieve the whole lot at one go

    Select all these rows, right-click > Delete Row

    Remove the filter, and the remaining rows will be the desired non knock-off items

    Alternatively, just filter for blanks in col C -- these are the non knock-off items,

    then copy n paste special as values elsewhere

    2 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2012-02-18T20:47:38+00:00

    Say we have in columns A & B:

    26 79
    21 80
    92 45
    28 39
    56 62
    98 49
    83 66
    92 53
    7 45
    53 70
    38 38
    56 46
    87 48
    44 78
    77 33
    93 79
    15 65
    5 55
    69 71
    2 60
    70 57
    64 77
    29 77
    68 45
    54 69
    98 67
    80 54
    2 47
    53 55
    51 68

    There are a number of values that  appear in both column A and column B.  Running this macro:

    Sub RemoveCommon()

    Dim A As Range, B As Range

    Dim ra As Range, rb As Range

    Dim rDel As Range

    Set A = Range("A1:A30")

    Set B = Range("B1:B30")

    For Each ra In A

        va = ra.Value

        For Each rb In B

            If rb.Value = va Then

                ra.Value = "xx"

                rb.Value = "xx"

                GoTo onepair

            End If

        Next

    onepair:

    Next

    Set rDel = Nothing

    For Each r In Union(A, B)

        If r.Value = "xx" Then

            If rDel Is Nothing Then

                Set rDel = r

            Else

                Set rDel = Union(r, rDel)

            End If

        End If

    Next

    If rDel Is Nothing Then

    Else

        rDel.Delete shift:=xlUp

    End If

    End Sub

    will remove those values from both columns leaving:

    26 79
    21 45
    92 39
    28 62
    56 49
    98 66
    83 45
    92 46
    7 48
    56 78
    87 33
    44 79
    93 65
    15 55
    5 71
    2 60
    64 57
    29 77
    98 45
    2 67
    53 47
    51 55

    this is only a sample, it would have to be modified for your specifics.

    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

    1. paste the stuff in and close the VBE window

    If you save the workbook, the macro will be saved with it.

    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

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-02-18T18:33:14+00:00

    What do you mean by "pull out"... do you want the "matched" data deleted, or do you have something else in mind? also, what column are your debits and credits in (we might as well address your real locations instead of guessing wrong and making you change it)?

    0 comments