Results 1 to 16 of 16

Thread: Compare the numbers in 2 columns and reconcile

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Compare the numbers in 2 columns and reconcile

    Hi, I am trying to compare 2 columns and find the match of numbers e.g. column A has 108.33 value and column B has the split of the same value .i.e. 33.33, 50 and 25 so the macro should loop through column A and B and find such matches and color code them into one unique color in both column A and B.


    I have come across the below macro through my search but this is to find the exact matches. can someone help.

    Code:
    Sub Compare_Ranges()
    'On Error Resume Next
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rCell As Range
    Dim result As Byte
    
    Set rng1 = ThisWorkbook.Sheets("Sheet1").Columns("A:A")
    Set rng2 = ThisWorkbook.Sheets("Sheet1").Columns("B:B")
    For Each rCell In rng1
    rCell.Interior.ColorIndex = xlNone
    rCell.Validation.Delete
    result = WorksheetFunction.CountIf(rng2, rCell)
    If result = 0 Then
    rCell.Interior.ColorIndex = xlNone
    ElseIf result = 1 Then
    rCell.Interior.Color = vbGreen
    With rCell.Validation
    .Add xlValidateInputOnly
    .InputMessage = "The value is " & result & "time occured in " & rng2.Address & "."
    End With
    
    ElseIf result = 2 Then
    rCell.Interior.Color = vbYellow
    With rCell.Validation
    .Add xlValidateInputOnly
    .InputMessage = "The value is " & result & " time occured."
    End With
    
    ElseIf result = 3 Then
    rCell.Interior.Color = vbBlue
    With rCell.Validation
    .Add xlValidateInputOnly
    .InputMessage = "The value is " & result & " time occured."
    End With
    ElseIf result = 4 Then
    rCell.Interior.Color = vblavender
    With rCell.Validation
    .Add xlValidateInputOnly
    .InputMessage = "The value is " & result & " time occured."
    End With
    
    End If
    Next
    End Sub
    Attached Images Attached Images  
    Last edited by abhay_547; Feb 10th, 2018 at 03:38 PM.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    can someone please help with the above code.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    can someone please help with the above code.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Compare the numbers in 2 columns and reconcile

    i think it is way over my pay grade
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Compare the numbers in 2 columns and reconcile

    Out of curiosity, how many rows might there be in column B? Is it just the nine that you show, or could it be hundreds or even thousands?

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    Quote Originally Posted by vbfbryce View Post
    Out of curiosity, how many rows might there be in column B? Is it just the nine that you show, or could it be hundreds or even thousands?

    yep, it could be close to thousand for B and little bit less then that for A since it consists of total value and B consists of split of that total number.
    Last edited by abhay_547; Feb 12th, 2018 at 02:02 PM.

  7. #7
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    There are still some unanswered questions as to what you are looking for.

    If a value in column B is "used", meaning it has already been identified as being part of a group of numbers in column B that sum to a value in column A, then I'm assuming that value in column B should be considered "taken", meaning it cannot be reused in another sum.

    For example, if the data were like this:

    Code:
        A    B
    1   2    1
    2   1    1
    3        1
    Then the 2 in A1 would be linked to the 1 in B1 and the 1 in B2, and the 1 in A2 would be tied to the 1 in B3. That's the only way that the color coding would work right, so I'm assuming that is the intent.

    What is the upper bounds on how many cells in column B can be included in a single sum? Your example shows 3 cells in B, but are you always looking for exactly 3, or are you looking for 1 or 2 or 3 or more?

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    usually It could be a split of 3 or 4 but can be more in certain cases in column B. The column A and column B are sourced from 2 different places and this is more sort of reconciliation .i.e. we are trying to find the match for the sum total which appears in column A. according to our underlying data provider team, the split numbers in column B are unique so if you loop through column B and keep adding numbers until you find the match for the total in column A, that should give the match for each total in column A.

  9. #9
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    Ok, well I'm not able to devote time to writing any code to help you with this problem. But what you are looking to accomplish seems to be anything but trivial. A quick example of how this spins out of control quickly as column B increases in length. Let's create a spreadsheet with only 1 value in column A, and 1000 values in column B.

    Say that cell A1 contains the number 1200, and that we've hard-coded the fact that there will always be 3 values in Column B somewhere that will add up to each value in A1. Note that this is actually a simpler problem than what you say, since you say that sometimes it can be 4 or more values need from column B.

    Ok, so we Add up B1, B2 and B3. 1200? Nope. Then we add up B1, B2, and B4. 1200? Nope......
    ...Then we add up B1, B2, and B1000. 1200? Nope. We've just done 998 calculations.
    Then we add up B1, B3, B4. Nope. Then we add up B1, B3, B5. Nope....
    ...Then we add up B1, B3, B1000. Nope. We've just done 997 more calculations...........
    ...Then we add up B1, B999, B1000. Nope. In total, we've just done nearly 500,000 calculations and what have we determined?

    That the value in B1 isn't part of the sum of A1. That's it. Let that sink in for a second...

    Then we move on adding B2, B3, and B4...and so on.

    1000 choose 3 = 166,167,000. That is the "worst case scenario" of how many comparisons would need to be done to find the three values out of 1000 that sum to one specific number.

    Now, as matches are found, the total pool of possible numbers from column B decreases, meaning each successive matching of values in A->B should on average go faster than the previous one. And if values in Column B are duplicated (like in your example, 50 shows up twice), then that would make the process quicker as well.

    But then you add in the caveat that sometimes there might be 4 or more values in column B that are necessary to group together to equal a value in column A...Nope, nope, nope, I'm outta here.

    Good luck.

  10. #10
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    Also, looking at this strictly from the "color coding" standpoint, if you are talking about having 1000+ items in Column B, that means that there would need to be several hundred unique colors used to color code the matches. If the goal is to make the resulting report so that you can simply look at it and have the matching values from columns A and B stand out by being the same color, then obviously the more colors you use the harder that is since you will have to use colors that are "close" to other colors.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    agreed. Color is one option which i was looking at for identifying the values but it seems to be difficult and also as you mentioned this could get lot more complicated if the split value count is more than 4 or undefined. would appreciate if you can share any other approach which will help to find the matches more quickly. Other than the color coding of column A and B by looping through each row

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Compare the numbers in 2 columns and reconcile

    From your post #8:

    The column A and column B are sourced from 2 different places and this is more sort of reconciliation
    At the time the numbers are written to columns A & B, is there any "key" information available to write? For example, if column A were totals by customer, and column B were specific PO amounts (which tie to a customer), could the customer number in each case be written as well, so you could match on that?

  13. #13
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    Quote Originally Posted by abhay_547 View Post
    agreed. Color is one option which i was looking at for identifying the values but it seems to be difficult and also as you mentioned this could get lot more complicated if the split value count is more than 4 or undefined. would appreciate if you can share any other approach which will help to find the matches more quickly. Other than the color coding of column A and B by looping through each row
    If you need to match 4 items out of a pool of 1000 items, there are over 41 billion possibilities.
    If you need to match 5 items out of a pool of 1000 items, there are over 8.25 trillion possibilities.
    If you need to match "unknown number" of items out of 1000, there are "unknown poop-tons" of possibilities.

    I have nothing to suggest here, other than to look at any other linking data you might have between the values in columns A and B, as pointed out in post #12 above. Blindly matching values on the scale you are talking about isn't feasible in my opinion.

  14. #14
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    For the sake of my own curiosity, I wrote a quick VB 6 program to simulate some of this. All my program does is it takes the numbers from 1 to n (where I'm defining n), adds them up in groups of 3, so 1+2+3=6, 4+5+6=15, and so on. It adds the sum values to one listbox, and the numbers themselves into another listbox, but in random order. So, for n=6, they might look like this:

    Code:
    lstSums
    6
    15
    
    lstPieces
    6
    1
    4
    5
    2
    3
    Then, using nested loops, it examines the values in lstPieces 3 items at a time, looking for a group of three items that sum to the items in lstSums. When found, it moves those pieces to a new listbox to remove them from the total pool of pieces.

    When n = 9, the results are instantaneous (not unexpected)
    When n = 99, the results take about 4 seconds (not unexpected)
    When n = 300, the results took about 600 seconds...

    If I was needing to write this for my own purposes, I know there is extra logic that I could add to make it run slightly faster in some cases, but it would probably be along the order of single digit percent improvements in total time taken.

    I'm not going to test any higher. Again, that's with very controlled data, and with hard-coding the "piece count" to 3. I'm not even sure how to write the code that could handle an "unknown" number of pieces, and I'm not going to spend time thinking about it either.

  15. #15
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Compare the numbers in 2 columns and reconcile

    Probably my last post on this, but if I were to make one suggestion, it would be to make sure that the values in both columns A and B are sorted in ascending order to start with. That way, if you are using nested loops to compare items sequentially, it would let you add some logic to determine if you are past the point at which a sum is possible for a given value, then you can terminate a loop early and avoid making unnecessary comparisons.

    For the record, I added some additional checks to my test program and got the group of 100 sums and 300 pieces to match up in about 30 seconds, so the performance gain was substantially better than I had thought. But again, I'm using controlled contrived data and hard-coding the piece count to 3. Not going to touch 1000 pieces, not going to touch unknown piece counts.

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Compare the numbers in 2 columns and reconcile

    ok. thanks. I will use the sort method but as vbfbryce had mentioned in his comment, I will also try to check with the data provider if we can get some additional criteria which can be used to do this comparison, which can make this job lot easier. Right now there is no criteria which is common between both the datasets which is the major problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width