Compare negative and positive value in 2 columns

[abjac]

Hi I have below sample file. In column H i have plus amounts, then i have a free column I, And then i have column J with minus amounts.
The total range in the below sheet is from A:O, with a free column in the middel(Column I)
I need a macro which can compare the plus and minus amounts in column H and J. And when it find a pair of matching numbers. I shall highlight the cells with the numbers.

The special is that i need to be sure that it only highlight when there is a pair.
Means if i have 3. of 36,36,36, and only 2. of -36,-36
It shall only highlight, the 2 of the 36, because it pair with 2 negative of the same amount.
Its very important it work like this.
I dont know if the free column I, can be used to somthing smart, but i am open to try some good ideas.
But i would prefer a macro to do this.
If its possible to fix this. I would like to have another macro after, which copy all the rows, where there is a highlighted cell, to sheet 2.

Hope some have a great solution for this.

Have a look at the attached file

Thanks in advance

Sincerely

Abjac

[xladept]

see if this is what you want and I'll try to speed it up - if I can.

Please Login or Register  to view this content.

[abjac]

HI Xladept. As usual from this is working perfect. It does what it have to do and your correct the speed could be a little faster, but infact its ok
One question. If i wanted to copy not the one who are highlighted, but the opposit.
Copy the rows which are not highlighted. How would the code Sub Abjac2 look then?
By thanks for putting my name there, should nearly be yours.

Please have a look if you can handle the opposit copy code.
Thanks for now

Abjac

[abjac]

HI Found the solution the oposit way just to exchange it with this.

Please Login or Register  to view this content.

So its working fine. What i have just done manually, which would be good to have in a macro is.

That H as range has a range from column A to H ( Positive amounts,And copy the highlighted rows from this range to the same range in Sheet 3)
and then that J as Range has a range from column J to O.(Negative amount, And copy the highlighted rows from this range to the same range in Sheet 3)


Then i have the yellow marked up against each otherPositive in column H and negative in column J, all with the complete row inside the set range. Which does it really easy to have an overview to see and compare, when you have to balance.

And i can change it so i have the same against the ones who dont have a pair.

This would really be great if you can change the code, so it can be like this.
I did it manually now. An the result is so good, when you have many numbers to compare.

So please have a look xldept, and see if you have an idea, how this can be made.

Thanks in advance

Abjac

[xladept]

Hi Abjac,

Try this on the raw sample:

Please Login or Register  to view this content.

[abjac]

hi xladept. Its working, just it run all in one macro. When i run it, it highlight the matches in Sheet1, which is fine, and then it copy the highlighted to Sheet 3, In the seperated range. Its perfect very easy to see what have to be matched.
After that i this time have more problems.
I need to basically do the same for the one who are xlColorIndexNone.
But this time i cant figure out how to set this up here.
So if you can make the same, but where it copy the xlColorIndexNone cells to sheet 4 example, then i will have also the list up against each other, which cannot be matched. It will really be perfect.

Please see if you can put this variable macro in also.

Thanks in advance

Sincerely

Abjac

[abjac]

hi xladept. Its working, just it run all in one macro. When i run it, it highlight the matches in Sheet1, which is fine, and then it copy the highlighted to Sheet 3, In the seperated range. Its perfect very easy to see what have to be matched.
After that i this time have more problems.
I need to basically do the same for the one who are xlColorIndexNone.
But this time i cant figure out how to set this up here.
So if you can make the same, but where it copy the xlColorIndexNone cells to sheet 4 example, then i will have also the list up against each other, which cannot be matched. It will really be perfect.

Please see if you can put this variable macro in also.

Thanks in advance

Sincerely

Abjac

[xladept]

Hi Abjac,

It ran on the raw sample for me are you using the same sample from your first post?? It's slow!

Try this:

Please Login or Register  to view this content.

[abjac]

HI Xladept.

The first code is working good. After i run it it copy to sheet 3 for all the items which have to be matched.
The second code is not working make the wrong result,special in column J. the same number all the way down.

But its ok with the first, even if a little slower.

But i would like to have a code, which instead of copy the highlighed to sheet 3.

Copy the non highlighted to sheet 4. Still with the seperated range.

The i will have in sheet 3 the ones, i can match.

And in sheet 4 the ones, i cant match.(I need those to balance later on against open invoices)

So if you can make the second code for the non highlighted, it will be really great.And yes i use this smple sheet only for the test.

Thanks in advance

Sincerely Abjac

[xladept]

Hi Abjac,

Here's the corrected version:

Please Login or Register  to view this content.

[abjac]

Thanks allot xladept. It is working
I first run code from post 5. Its a little slow around 5 min, but it does the job. It copy the yellow match to sheet 3 and highlight in sheet1.

Then i run your code here from post 10. Its working and are faster around 1 min and it copy all the non highligted to sheet 4.
So now i can compare it. Really great. Thanks allot. Its really usefull to me this.
Sincerely

Abjac

[xladept]

You're welcome - I'm still trying to make it faster but I think that coloring the cells is time consuming!

[abjac]

HI Xladept. Yes maybe your right about that. If you can make it without the coloring, it would also be ok. I didnt know it would slow it down. But thought it would be more easy to see the matches. But if you can make it without, it would be ok. So dont hold your self back with that

Have a nice day

Sincerely

Abjac

[xladept]

Hi Abjac,

Here's a more usable version, it runs in seconds rather than minutes - let me know of any issues

Please Login or Register  to view this content.

[abjac]

Hi Xladept. Wow what a fast code it work like a charm. I only need one thing to be corrected. It remove the zeroes after the decimal.
12,00 will be 12
0,50 will be 0,5
72,00 will be 72
I need the zeroes after, Specially later one, when i need to use these numbers in another application.
But your code so fast, so if you can solve the last its really a perfect and great code.
So dont stop Please solve the last. Hope you can

Sincerely
Abjac

[xladept]

Hi Abjac,

Here's this - I'll look at the other:

Please Login or Register  to view this content.

[abjac]

Hi Xladept. This code work also very fast and do the thing.
Could i ask you to look at below sample sheet. I would like to use a commandbutton in sheet1 and just let the code run from the existing date in sheet 2 to 3 and 4.
That is how i have to run in real, and i have to admit your code is out off my range of macro skills haha.

Would be nice.
Another thing is when i run it, its fast and good. But it seems it cut a row somewhere. I think it must be in sheet 4.
I can calculate from the first sheet and forward. The magic number is 2470.
Thats what we have when i calculate the plus side (column A to H) + the negative side ( Column J to O)
So this number also have to match after finish the macro.
But please see if you ahve time and lust to solve the first and then lets take it with the other after.

But your code is fast really fast, so i hope you can do it

Thanks

Sincerely
Abjac

[xladept]

Hi Abjac,

I did drop a row - try this: AbjacCT.xls

Please Login or Register  to view this content.

I'm working the code for the last sample - here is that code:

Please Login or Register  to view this content.

The code with the fix takes 15 lines - a simpler version takes two sets of five lines - so,in the sake of simplicity, you may want to replace:

Please Login or Register  to view this content.

With this:

Please Login or Register  to view this content.

[abjac]

Hi.Xladept. Wow. I have checked them all now. And my word is wow. You have just changed this code from taking 8 min to around 5 seconds.
I also checked the rows and it all match now.
So what can i say. I am really greatfull for this. That you wanted to use your time on this. I will use this code directly today in my work. And it will help me allot.
Thanks allot for taking you time and helping out with this. Wow great code

Have a nice day

Sincerely
Abjac

[xladept]

You're welcome! You're working long hours, I also appreciate the timely feedback

[abjac]

haha I think there is some big timedifference from Madrid to california. But yes have been working quite allot with this. And yes i really appriciate this help from all in this forum.
Great answers and also many times fast. So its really a good forum.
So just keep up the great work and thanks again

[abjac]

Hi Xladept. A little cosmetic thing. When it make the compare to the other sheets. It copy the header also, which is great. Any idea how it can example colour the header it copy to colour 45. So it have the same color as the other headers. By the way i have now used your code. And it really saved me for so much time. So its really great. Its part of a bigger sheet with more macroes. So its really great. have a look at this if you have time. Its only cosmetic.

Have a nice day

Sincerely Abjac

[xladept]

Hi Abjac,

The color is 35

Please Login or Register  to view this content.

[abjac]

HI Xladept.
Thanks allot it works like a charm. Perfect

Have a nice day

Sincerely

Abjac

[xladept]

You're welcome!