Debits and Credits to offset in a column

K

kjaan

New Member
Joined
Feb 2, 2011
Messages
20
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
 
Sort by date Sort by votes

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Got the file, thanks.

I'll will have to look at this later, but I do have some questions:

1. So there's only two columns (that's not what you said earlier)?
2. Is the reference number in column B only ever there twice as a maximum i.e. once for the positive amount and the other for the negative amount?
3. How does the correct list start with an amount of -627?

Robert
 
Upvote 0
Trebor76 said:
Got the file, thanks.

I'll will have to look at this later, but I do have some questions:

1. So there's only two columns (that's not what you said earlier)?
2. Is the reference number in column B only ever there twice as a maximum i.e. once for the positive amount and the other for the negative amount?
3. How does the correct list start with an amount of -627?

Robert
Click to expand...

1.Yes there should be only two columns.
2.No the same reference number can appear numerous times.
3.List can start with any number because the list is sorted by reference number, from smallest to largest, so we have better overview of matching items.

I hope this helps.
 
Upvote 0
See how this goes:

Code: 
Option Explicit
Sub Macro4()

    'Matches reference numbers in column B where the amount for that reference number is initially positive

    Dim lngEndRow    As Long
    Dim rngMyCell    As Range
    Dim rngMatchCell As Range
    
    Application.ScreenUpdating = False
    
    'Find the last row across columns A and B
    lngEndRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Sort the range by reference number (lowest to highest) and amount (highest to lowest)
    Range("A1:B" & lngEndRow).Sort key1:=Range("B1:B" & lngEndRow), order1:=xlAscending, Header:=xlNo, key2:=Range("A1:A" & lngEndRow), order2:=xlDescending, Header:=xlNo
    
    For Each rngMyCell In Range("A1:A" & lngEndRow) 'Amount column
        If Val(rngMyCell) > 0 Then  'Only try and match the first reference if its amount is positive
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(Range("A" & rngMatchCell.Row)) * -1 = Val(Range("A" & rngMyCell.Row)) And Range("B" & rngMatchCell.Row) = Range("B" & rngMyCell.Row) Then
                    With Range("A" & rngMyCell.Row & ":B" & rngMyCell.Row)
                        .Copy Destination:=Range("C" & rngMyCell.Row)
                        .ClearContents
                    End With
                    With Range("A" & rngMatchCell.Row & ":B" & rngMatchCell.Row)
                        .Copy Destination:=Range("C" & rngMatchCell.Row)
                        .ClearContents
                    End With
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbInformation
    
End Sub

Robert
 
Upvote 0
Trebor76 said:
See how this goes:

Code: 
Option Explicit
Sub Macro4()

    'Matches reference numbers in column B where the amount for that reference number is initially positive

    Dim lngEndRow    As Long
    Dim rngMyCell    As Range
    Dim rngMatchCell As Range
    
    Application.ScreenUpdating = False
    
    'Find the last row across columns A and B
    lngEndRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Sort the range by reference number (lowest to highest) and amount (highest to lowest)
    Range("A1:B" & lngEndRow).Sort key1:=Range("B1:B" & lngEndRow), order1:=xlAscending, Header:=xlNo, key2:=Range("A1:A" & lngEndRow), order2:=xlDescending, Header:=xlNo
    
    For Each rngMyCell In Range("A1:A" & lngEndRow) 'Amount column
        If Val(rngMyCell) > 0 Then  'Only try and match the first reference if its amount is positive
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(Range("A" & rngMatchCell.Row)) * -1 = Val(Range("A" & rngMyCell.Row)) And Range("B" & rngMatchCell.Row) = Range("B" & rngMyCell.Row) Then
                    With Range("A" & rngMyCell.Row & ":B" & rngMyCell.Row)
                        .Copy Destination:=Range("C" & rngMyCell.Row)
                        .ClearContents
                    End With
                    With Range("A" & rngMatchCell.Row & ":B" & rngMatchCell.Row)
                        .Copy Destination:=Range("C" & rngMatchCell.Row)
                        .ClearContents
                    End With
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbInformation
    
End Sub

Robert
Click to expand...

Hi Robert,

I am afraid it's still not good, it only switches the positions of negative and positive values with the same references and then matches them.

And there's no need macro to sort references from smallest to largest as I have already done that in previous steps.
 
Upvote 0
I am afraid it's still not good, it only switches the positions of negative and positive values with the same references and then matches them.
Click to expand...

I'm confused - in your original thread you said "For example in column A we have amounts and in column B we have reference numbers, so can you make a Macro that will match only credit and debit that have the same reference number?". That's exactly what the latest macro does except that it will only do that if the amount is positive to start?

And there's no need macro to sort references from smallest to largest as I have already done that in previous steps.
Click to expand...

I needed to do that so the data in Sheet1 is in that order as you didn't sort that way or mention that you do when you posted the workbook :confused:

Which lines are out - the result of my macro has matched the same as your Correct tab ??

Also please don't quote entire posts as just creates clutter. Only quote specific parts that add to highlighting a point.

As I'm obviously not understanding your requirements I will stop posting to this thread. Start a new thread with a link back to this post if you think it will help.

Good luck with it.

Robert
 
Last edited:
Upvote 0
I didnt know until recently that I have to match credit and debit only if debit comes first.
I think that I already sorted that file by ref number and unfortunately this newest macro matches all positive and negative values regardless if negative one comes first in a column.
I am sorry, as we were so close with that one macro that is in the file I posted and that bolds values and does the matchig. That macro matches credits and debits only if positive one is first but I spoted that small mistake when it matched only one pair out of 1000 invoices with negative value first.
Maybe I cant explain properly what i exactly need as well.
I will start a new thread, with that macro as a starting point and ask if someone can help to solve that small part that is not covered by macro.
All the best!
 
Last edited:
Upvote 0
I've added the bolding negative numbers on the attached and commented out the sorting:

Code: 
Option Explicit
Sub Macro4()

    'Matches reference numbers in column B where the amount for that reference number is initially positive

    Dim lngEndRow    As Long
    Dim rngMyCell    As Range
    Dim rngMatchCell As Range
    
    Application.ScreenUpdating = False
    
    'Find the last row across columns A and B
    'lngEndRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Sort the range by reference number (lowest to highest) and amount (highest to lowest)
    Range("A1:B" & lngEndRow).Sort key1:=Range("B1:B" & lngEndRow), order1:=xlAscending, Header:=xlNo, key2:=Range("A1:A" & lngEndRow), order2:=xlDescending, Header:=xlNo
    
    For Each rngMyCell In Range("A1:A" & lngEndRow) 'Amount column
        If Val(rngMyCell) > 0 And rngMyCell.Font.Bold = False And Len(rngMyCell) > 0 Then 'Only try and match the first reference if its amount is positive
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(Range("A" & rngMatchCell.Row)) * -1 = Val(Range("A" & rngMyCell.Row)) And Range("B" & rngMatchCell.Row) = Range("B" & rngMyCell.Row) Then
                    With Range("A" & rngMyCell.Row & ":B" & rngMyCell.Row)
                        .Copy Destination:=Range("C" & rngMyCell.Row)
                        .ClearContents
                    End With
                    With Range("A" & rngMatchCell.Row & ":B" & rngMatchCell.Row)
                        .Copy Destination:=Range("C" & rngMatchCell.Row)
                        .ClearContents
                    End With
                    Exit For
                End If
            Next rngMatchCell
        ElseIf Val(rngMyCell) < 0 And Len(rngMyCell) > 0 Then
            rngMyCell.Font.Bold = True
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(rngMatchCell) * -1 = Val(rngMyCell) Then
                    rngMatchCell.Font.Bold = True
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbInformation
    
End Sub

After the macro ran the total in column A reconciles between the tabs Sheet1 and Correct with both having a total of $973,787.96. So that says to me we must be very close. The total in column C for both sheets is naturally zero. If the macro is not matching any particular number, list them by exact amount.

Robert
 
Last edited:
Upvote 0
Sorry, try this one:

Code: 
Option Explicit
Sub Macro5()

    'Matches reference numbers in column B where the amount for that reference number is initially positive

    Dim lngEndRow    As Long
    Dim rngMyCell    As Range
    Dim rngMatchCell As Range
    
    Application.ScreenUpdating = False
    
    'Find the last row across columns A and B
    lngEndRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'Sort the range by reference number (lowest to highest) and amount (highest to lowest)
    'Range("A1:B" & lngEndRow).Sort key1:=Range("B1:B" & lngEndRow), order1:=xlAscending, Header:=xlNo, key2:=Range("A1:A" & lngEndRow), order2:=xlDescending, Header:=xlNo
    
    For Each rngMyCell In Range("A1:A" & lngEndRow) 'Amount column
        If Val(rngMyCell) > 0 And rngMyCell.Font.Bold = False And Len(rngMyCell) > 0 Then 'Only try and match the first reference if its amount is positive
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(Range("A" & rngMatchCell.Row)) * -1 = Val(Range("A" & rngMyCell.Row)) And Range("B" & rngMatchCell.Row) = Range("B" & rngMyCell.Row) Then
                    With Range("A" & rngMyCell.Row & ":B" & rngMyCell.Row)
                        .Copy Destination:=Range("C" & rngMyCell.Row)
                        .ClearContents
                    End With
                    With Range("A" & rngMatchCell.Row & ":B" & rngMatchCell.Row)
                        .Copy Destination:=Range("C" & rngMatchCell.Row)
                        .ClearContents
                    End With
                    Exit For
                End If
            Next rngMatchCell
        ElseIf Val(rngMyCell) < 0 And Len(rngMyCell) > 0 Then
            rngMyCell.Font.Bold = True
            For Each rngMatchCell In Range("A1:A" & lngEndRow)
                If Val(rngMatchCell) * -1 = Val(rngMyCell) Then
                    rngMatchCell.Font.Bold = True
                    Exit For
                End If
            Next rngMatchCell
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Done", vbInformation
    
End Sub

Robert
 
Upvote 0
Yes, we are close, but again we have that mistake with value of 240, and that's the only problem.
It matched first negative and positive value instead of positive and negative.
Maybe this value 240 above with different reference somehow confuses it.
I tried to send you a screen shot but could not inset the image.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Create .dat file from excel data for payment upload
Replies
1
Views
83
sailingmike49
S
A
  • Question
Editable search
Replies
2
Views
152
Aqusmacro
A
L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
475
LookingForHelp1
L
M
  • Solved
Assistance Needed with Excel VBA Code for Color Formatting
2
Replies
10
Views
807
RudRud
R
S
  • Question
Using criteria to sum data from another sheet.
Replies
1
Views
361
SandieConfused
S

Forum statistics

Threads
1,215,780
Messages
6,126,857
Members
449,345
Latest member
CharlieDP

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top