Announcement

Collapse
No announcement yet.

Match positive and negative values withing subgroups

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match positive and negative values withing subgroups



    Hi,

    I have a sheet which contains credits and debits for several accounts:

    Customer Amount
    ABC 2000
    XYZ 3000
    XYZ 1000
    ABC 1000
    ABC -5000
    XYZ 1000



    What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and this forums to match within all rows.

    Code:
    Sub matchdata()
    On Error Resume Next
    
    
    For Each cl In Columns(10).SpecialCells(2, 1)
    Err.Clear
    x = Columns(1).Find(-cl.Value, cl.Offset(1), xlValues, 1).Activate
    If Err.Number = 0 Then
    With Selection.Interior
    .ColorIndex = 2
    End With
    cl.Interior.ColorIndex = 2
    End If
    Next
    End Sub
    I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.

    Please help!!

  • #2
    Re: Match positive and negative values withing subgroups

    You could use conditional formatting with this formula

    =COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,-1*$B2)>0

    Comment


    • #3
      Re: Match positive and negative values withing subgroups

      Thanks Stephen but I can't get this to work.

      Comment


      • #4
        Re: Match positive and negative values withing subgroups

        That is not a very helpful response. Can you elaborate?

        Comment


        • #5
          Re: Match positive and negative values withing subgroups

          Apologies Stephen, for not being clear. I just figured that this will work. Putting the formula in a new column marks the ones that need to be highlighted. Somehow, It is not working when I enter the formula in conditional formatting.

          However, I would love a VBA solution for two reasons:

          1. I have changed my code to highlight using different color each time. This will help because I am looking at a set of over 25,000 rows and many highlights will cause confusion.

          2. In the next step, I wish to offset several credits against a single debit. In the example, ABC -5000 would offset ABC 1000 and ABC 2000 both. Not bothered about the balance.

          Comment


          • #6
            Re: Match positive and negative values withing subgroups

            Ok your second point suggests it is a more complicated problem than the one I envisaged. That formula only finds matching amounts such as 100 and -100.

            Comment


            • #7
              Re: Match positive and negative values withing subgroups

              Stephen, there seems to be a small problem with the formula you mentioned. It highlights multiple debits against a single credit.
              e.g. it highlights all ABC 1000 against a single ABC -1000.

              Comment


              • #8
                Re: Match positive and negative values withing subgroups

                Bump!

                Comment


                • #9
                  Re: Match positive and negative values withing subgroups

                  Yes, it would. I think on reflection I would use VBA for this. You need someone much better than me at formulae if you want that approach!

                  Can you post a sample workbook and I will try some code?

                  Comment


                  • #10
                    Re: Match positive and negative values withing subgroups

                    Originally posted by StephenR View Post
                    Yes, it would. I think on reflection I would use VBA for this. You need someone much better than me at formulae if you want that approach!

                    Can you post a sample workbook and I will try some code?
                    Apologies for the late response. My company network blocks uploading any files so I have pasted a sample.

                    Diwan Furnishings 235900
                    VIP Industries 18000
                    Diwan Furnishings 11500
                    Diwan Furnishings -11500
                    Okaya Batteries 43700
                    Diwan Furnishings 11500
                    VIP Industries -18000
                    VIP Industries 18000
                    Thanks for the help.

                    Comment


                    • #11
                      Re: Match positive and negative values withing subgroups

                      I did a mock up and this appeared to work:
                      Code:
                      Sub x()
                        
                      Dim rFind As Range, sAddr As String, r As Range, s() As String, i As Long, a, b
                       
                      With Range("A2", Range("A" & Rows.Count).End(xlUp))
                          ReDim s(1 To 2 * .Count)
                          For Each r In .Cells
                              Set rFind = .Find(What:=r, After:=r, LookAt:=xlWhole, searchdirection:=xlNext, _
                                                MatchCase:=False, SearchFormat:=False)
                              If Not rFind Is Nothing Then
                                  sAddr = rFind.Address
                                  Do
                                      If r.Offset(, 1) = -1 * rFind.Offset(, 1) Then
                                          a = Application.Match(r.Address, Application.Index(s, 1, 0), 0)
                                          b = Application.Match(rFind.Address, Application.Index(s, 1, 0), 0)
                                          If Not IsNumeric(a) And Not IsNumeric(b) Then
                                                  r.Resize(, 2).Interior.Color = vbYellow
                                                  rFind.Resize(, 2).Interior.Color = vbYellow
                                                   i = i + 1
                                                  s(i) = r.Address
                                                  s(i + 1) = rFind.Address
                                                  i = i + 1
                                                  Exit Do
                                          End If
                                      End If
                                      Set rFind = .FindNext(rFind)
                                  Loop While rFind.Address <> sAddr And rFind.Address <> r.Address
                              End If
                          Set rFind = Nothing
                          sAddr = ""
                          Next r
                      End With
                           
                      End Sub
                      Can you download files?

                      Comment


                      • #12
                        Re: Match positive and negative values withing subgroups

                        Thanks Stephen. I'll try this now.

                        Yes I can download files

                        Comment


                        • #13
                          Re: Match positive and negative values withing subgroups

                          Originally posted by StephenR View Post
                          I did a mock up and this appeared to work

                          Thanks Stephen!!! Works like magic. Thank you for staying with me through the problem.

                          Comment


                          • #14


                            Hi StephenR - running your macro on data-set of ~ 19000 rows, after 11 minutes had to stop - turned out it only went through ~2300 rows
                            Is that an expected performance you'd say?

                            Comment

                            Working...
                            X