Debits and Credits to offset in a column

[kjaan]

Hi,
I have a large data that I exported in excel that include debit and credit in a column. I would like to find an easier way to pair the debit and credit to offset each other by a formula and easily find the variance. So the column would look something like this.

Column A
250
400
350
-250
410
-400
-350
600
-410

 

[jarjarbingie]

so does that mean the credits are in rows 2, 4, 6, 8... while debits are in 3, 5, 7, 9...?

Hi,
I have a large data that I exported in excel that include debit and credit in a column. I would like to find an easier way to pair the debit and credit to offset each other by a formula and easily find the variance. So the column would look something like this.

Column A
250
400
350
-250
410
-400
-350
600
-410

 

[kjaan]

so does that mean the credits are in rows 2, 4, 6, 8... while debits are in 3, 5, 7, 9...?

The debit is positive and credit is negative.

 

[jarjarbingie]

it would help to post the required output that you need..

The debit is positive and credit is negative.

 

[kjaan]

The other columns are irrelevant because I am trying to offset debit and credit to net zero.

 

[Trebor76]

Hi kjaan,

Try this where the matched amounts will be shifted to Col. B leaving only the unmatched amount(s) in Col. A:

Option Explicit
Sub Macro1()

    Dim lngEndRow As Long
    Dim rngMyCell As Range
    Dim rngMatchCell As Range
    
    lngEndRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("A1:A" & lngEndRow)
        If Len(rngMyCell) > 0 Then 'ignore blank cells either by design or that have had their value shifted by the following code
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(rngMatchCell) * -1 = Val(rngMyCell) Then
                    rngMyCell.Offset(0, 1).Value = rngMyCell
                    rngMatchCell.Offset(0, 1).Value = rngMatchCell
                    rngMyCell.ClearContents
                    rngMatchCell.ClearContents
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[kjaan]

Hi kjaan,

Try this where the matched amounts will be shifted to Col. B leaving only the unmatched amount(s) in Col. A:

Option Explicit
Sub Macro1()

    Dim lngEndRow As Long
    Dim rngMyCell As Range
    Dim rngMatchCell As Range
    
    lngEndRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("A1:A" & lngEndRow)
        If Len(rngMyCell) > 0 Then 'ignore blank cells either by design or that have had their value shifted by the following code
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(rngMatchCell) * -1 = Val(rngMyCell) Then
                    rngMyCell.Offset(0, 1).Value = rngMyCell
                    rngMatchCell.Offset(0, 1).Value = rngMatchCell
                    rngMyCell.ClearContents
                    rngMatchCell.ClearContents
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

I can't use vba because my work doesn't have option to use developer add-in.

 

[Jonmo1]

What is your expected result?

 

[kjaan]

What is your expected result?

My expected result is that I have to find the offsetting debit and credit in the columns by formula and find the variance balance. Once I narrow down the variance then I can research why variance exists.

 

[chicagocomputerclasses]

Well, the logic here is flawed, because unless you have transaction or GL Ids, if your data is missing one GL entry at some point it will all get messed up.

Otherwise here is the formula:

Your data starts at A1 and ends at A9
In B1 cell use:

=IFERROR(IF(ROWS(B$1:B1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A$1:A$9>0,ROW(A$1:A$9)),ROWS(B$1:B1)))),"")

In C1 cell use:

=IFERROR(IF(ROWS(B$1:B1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A$1:A$9<0,ROW(A$1:A$9)),ROWS(B$1:B1)))),"")

Apply both formulas using CTRL+SHIFT+ENTER, don't just use enter, and then fill down.