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

---

can someone please help with the above code.

can someone please help with the above code.

i think it is way over my pay grade

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?

Quote:

---

Originally Posted by vbfbryce

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.

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?

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.

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.

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.

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

From your post #8:

Quote:

---

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?

Quote:

---

Originally Posted by abhay_547

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.

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.

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.

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.