Compare columns and detect changes

[buhnen]

Hi all,

I need please some help with the following issue: As you can see in the attached excel file, I have two charts which I must compare with each other.

BMK, stands for "item ID" and "Page" means the page number in which the BMK is located.

The difference between the list1 and the list2 is that some elements (,this is, BMK´s) might have been deleted in one of the lists or simply assigned to another page number (placement change). What I need to, is to detect automatically these possible changes for the items between lists.

In the sheet "Results" you may see the way this has to be performed. Assigning conditionally cell-colour formats to the cells: red for an element which was deleted in one of the lists and blue when the element was moved from one page onto another one.

With the "Consolidate" command I´ve managed myself to detect elements which have been deleted, but this whole thing goes beyond my skills with excel, when simultaneously I want to detect the elements which have been re-located in different pages..and I´m afraid tha, I need a Macro for this. Here it is where I´d really appreciate your help.

Thanks in advances for the hints and answers,

Rob

[buhnen]

( Just some pictures to clarify what is the purpose....See above explanations )

[arlu1201]

This is the code you need -

Please Login or Register  to view this content.

[buhnen]

vow! Really thankfull to you! I will just try it now ....but only one more thing I just forgot to say before: The new BMK´s added, should also be taken in account, in green colour, with the same logic like for the other two cases

[arlu1201]

What do you mean by New BMKs? Do you mean the ones that will have an entry only in Itemlist2?

[buhnen]

What do you mean by New BMKs? Do you mean the ones that will have an entry only in Itemlist2?

An image is better than 100 words...

Yes, I mean just that ...new items (BMK´s) which were added in the Itemlist2

[arlu1201]

Slight modification to the code (I had already made provisions for it but given it the same red color). Now i have changed it to green. Try this and let me know -

Please Login or Register  to view this content.

[buhnen]

Slight modification to the code (I had already made provisions for it but given it the same red color). Now i have changed it to green. Try this and let me know -

Please Login or Register  to view this content.

Thanks Arlette, I give you feedback in a while

[buhnen]

hmmm...I´m yet a bit newbie....I´m running excel in a O.S in German language (which isn´t my own one)...is there maybe in this forum by any chance another thread explaining how to save and run a Macro?

[arlu1201]

I can explain it to you in English and maybe you can get some help to translate it. Open your excel file, then click Alt + F11. This will open a VBE window. On the left hand side, you will find Microsoft Excel Objects. Right click and say Insert -> Module. Copy the revised code i gave you to the right hand side blank window. Then come back to the excel sheet and go to File - Save As. From the dropdown below the name, select Excel Macro-Enabled (xlsm) and save the file. Then either put an image / button on the main page and assign the macro to it or just go to Developer - Macros - Run the macro in the list.

[buhnen]

I can explain it to you in English and maybe you can get some help to translate it. Open your excel file, then click Alt + F11. This will open a VBE window. On the left hand side, you will find Microsoft Excel Objects. Right click and say Insert -> Module. Copy the revised code i gave you to the right hand side blank window. Then come back to the excel sheet and go to File - Save As. From the dropdown below the name, select Excel Macro-Enabled (xlsm) and save the file. Then either put an image / button on the main page and assign the macro to it or just go to Developer - Macros - Run the macro in the list.

Yep, I did it right...but when running the Macro, I´ve got this error message (see attachement)

[arlu1201]

I am not facing that problem at all. I tried using your macro workbook that you have attached and i still dont get the error. Everything works well. Can you try running it again.

[buhnen]

I am not facing that problem at all. I tried using your macro workbook that you have attached and i still dont get the error. Everything works well. Can you try running it again.

mmm...weird, yes, I´ll try it one more time in another computer and tell you again...

[buhnen]

I am not facing that problem at all. I tried using your macro workbook that you have attached and i still dont get the error. Everything works well. Can you try running it again.

mmm...My Excel version is 2010. I´ve just tried it in another version 2007, in another computer, and I get the same error ....

[arlu1201]

Try pressing F9 on the sheet and also check the calculation mode under Formulas -> Calculation Options -> Automatic (in 2007).

[buhnen]

Try pressing F9 on the sheet and also check the calculation mode under Formulas -> Calculation Options -> Automatic (in 2007).

Now, this is really interesting: I´ve made again the excelbook beginning from zero, this is I have created a new one, and copied and pasted it your code. Then, I also added a slight modification: I have renamed the sheets , changing "itemlist1" for "Itemlist1" (according to how you wrote your code)...And now it works!! ))

Still, I´m gonna test it again with some more examples and will confirm you that it runs ok

And again , really saying thanks for your well done work!

[arlu1201]

Np at all. Glad it worked. It could be that the wprkbook you were using was corrupted.

[buhnen]

Np at all. Glad it worked. It could be that the wprkbook you were using was corrupted.

only one little issue, though it is not a critical one: When a BMK is entered twice in the lists, it will only appear once, in the Result´s list...but still, that´s not a major issue for the purpose of real use of this macro

[arlu1201]

Yes, its actually not an issue. I have programmed it that way. Are there chances of getting the same BMK twice in each of the sheets?

[buhnen]

Yes, sometimes it may happen that an ID is repeated in one same list ( can be repeated even more than twice)..so it could be interesting if in the result list, then it can appear as much times as it is repeated

[arlu1201]

ok, in that case, i will have to change the way it has been programmed. This will result in the code changing. Let me think this over and see what logic i can work with.

[buhnen]

ok, in that case, i will have to change the way it has been programmed. This will result in the code changing. Let me think this over and see what logic i can work with.

Ok Arlette, thanks again for the effort. As it is now, though, it works ok, but if you make the improvement then it will be better.