Compare and highlight value in column but for each customer number.

[abjac]

Hi I would like to ask if some can help me solve this problem.
In below testsheet i have in sheet2 alot of info about customer and invoices etc.

In column D i have the customer number followed by the customer name. Between each customer number i have a blank row.
I would like to have a vba code that compare the value for open amount in column G and if there is a match between invoice and / payment or creditnote. Then highlight it with a colour.

But the match shall only be found inside the customer number in column D.

In below example there is a match for customer (Column D) 00237595 - +Z-PA GI. And in column G (Open Amount) there is a match for these amounts 991 Invoice and the payment -991. I would prefer a macro to do this, because this is a step by step with many other macros.
When i press the button in Sheet1 it make the match in sheet 2

Please have a look in the attached sheet and see if you can help with a solution.
Thanks in advance
Abjac

[MarvinP]

Hi abjac,

See if a pivot table answer is good enough for you. No code needed.

[patel45]

Please Login or Register  to view this content.

[abjac]

Hi Marvin and Patel. Thanks for your answer.
The pivot table is good can be used, but it also cover items which dont have a match. But it can be set up i think. So its usefull. Thanks Marvin.

The code Patel you came with is perfectIts cover all i need and its working perfect, like many of the other codes you provide. So i think i will use this solution.

But thank you to both, Always many good solutions here and i learn everyday. So its great.

Will mark this as solved

Have a nice day to both of you

Sincerely

Abjac

[abjac]

Hi Marvin. I have a question according Pivot table. I am not expert i these tables, but infact use them sometimes. In this case, when i use Patels code, it highlight the cells, that have a match. After i have infact a pivot table to have a better overview. But what i would like to have, is it possible to import from the sheet, where i find th match and highlight it. To the Pivot table. So the matches also are highlighted here?
I have searched after that, but cant find some usefull about it. It would be a good solution. Then i have the good overview in a Pivot table and the matched from the excel sheet are also highlighted with a colour here.
Is this possible?

Sincerely
Abjac

[oeldere]

@abjac

You have marked your question solved, so I suppose you won't get any answers to your (new) question.

Mark you question (unsolved) and add an excel example, without confidentional information, and also add the desired result.

Maybe I will find time to look at it (I don't promise I will).

[abjac]

Hi Oeldere and Other. Sorry marked as unsolved again. I have made the new testsheet below.
Sheet1 One Button when i press it( Thanks to a Patels code) it find the matches in sheet 2,and highlight them with colour yellow.
I have made a pivot table from the result of Sheet2. The only thing i would like to have is that the cells, which are highlighted in Sheet2.
Also is highlighted in the Pivot table. So its fast to see the match. In real this table will be very big, so its nice to have the match highlighted.

Please have a look thanks.
Below is the excel file uploaded to test on.

Sincerely Abjac

[abjac]

Hi Patel, I was testing your code and i thought it was fine. But it only find matches if they are over or under eatch other in the column G.

So its not workin ok, sorry to say. If you see below test sheet the customer number in column D have matches in customer number

00237595 and in customer number 00133450, and also in customer number 00149700. I have moved the amounts now and it will not find them, even if its same customer number from column D. Please have a look and see what can be wrong.
This code i really need to be sure work. It have to search in many rows.

Please have a look at below testsheet.

Thanks in advance
Abjac

[patel45]

try this

Please Login or Register  to view this content.

[abjac]

Hi Patel. Your code is perfect. I tried in a big file with many rows and it find them all. So its working perfect.
When i search in the big file. It would be nice to copy found matches including all rows for this customer to sheet 3, with a blank row between the customer number.Then you can go to Sheet 3 and see all the matches. Can the code be changed so it works like this?

Thanks

Abjac

[patel45]

Please Login or Register  to view this content.

[abjac]

Thanks Patel. I will check this one out. I suddenly got another problem with the first code, it make an error, when i tried a bigger file. I have tried īto find out what it is, but cant solve it.
Below is test2 with more rows to check in. Please see if you can find out why it make an error.

See test2.xls
Thanks

Abjac

[patel45]

no error with 2010

[abjac]

Hi Patel its really special also. I use excel at my work and also here at home both is excel 2003. At work it come with and error. But her at home it dont give any error. I dont understand have tried to see if any different things but all look the same and its with this file here.
So i really dont understand why its not work at my work laptop.
But thanks for your answer. I hope i can make it work

Sincerely Abjac

[abjac]

Hi This is really a special thing. At my worklaptop this was not working. But on my homelaptop it was. I tried all kind of formatting, but nothing helped. I then saw that the regional settings not was the same. At my workinglaptop they 1000 was with comma and dot for the cent.
At my homelaptop it was the other way. So i changed the regional setting at my worklaptop. And its working now. Just still think its weird i could not do this with any formatting. But its working this way. If any have a good eksplanation about this, i would like to hear it. Will mark this as solved now.

Thanks for all the help

Sincerly
Abjac