Compare and mirror ranges

[Kenny Bones]

Ok, I've been trying to get this Workbook together and found that a macro is needed in order to achieve this. Basically there are three sheets in this workbook, one with a list of names, one with a set of items and one summary sheet.

Right now the code basically create a range for the two sheets and add them to the summary sheet. But it doesn't take duplicated into consideration. So, I want to the code to check if the item and name already exists on the summary sheet and update the values if they do. Basically, I want to mirror whatever is in the sheet "Articles" to the summary sheet.

Where do I even start? I know I can compare the ranges and find the .Address of the cell where it already exists. But exactly how to do just that? I got some help on this code, especially the part that inserts the data to the Summary sheet. But it inserts it all in one go and doesn't take duplicates into concideration.

The basic idea is that if a new user is added or removed from the Users sheet, those changes are reflected back at the Summary sheet, removing that name or adding it alltogether. The same goes with each item in the Articles sheet.

[watersev]

hi, Kenny Bones, please check attachment, run code "test". The code refers to duplicates part of your question

The Users and Articles are supposed to be identical. Change_sheet event commented for now.

[Kenny Bones]

This is working as expected I'll have to scan through the Summary sheet to mirror the users as well, but I think I can handle that my self I'll also rewrite your code so I can understand what's going on But I'll set this as solved now

[watersev]

please check attachment, if everything is accounted for. Try to add, delete users or/and articles and check summary sheet

[Kenny Bones]

Wow, the code is actually a bit smaller now as well I really really appreciate this! You rock!

[Kenny Bones]

I'm not sure if this is asking too much, but is it possible to keep the data from column D in the Summary sheet? That's supposed to be the amount of items for each person. And that data is wiped whenever the macro is run. I tried to comment out that line and that kept the data in column D from being lost.

However, if I apply sorting/autofilter to each column in the Summary sheet, add some number on each cell in column D, rerun the macro code and then sort from ascending to descending, the data in column D doesn't "fit" it's corresponding person. And that's probably because the macro inserts the users based on the Users sheet. And that sheet isn't sorted the same way.

So, the smartest thing to do would probably be to sort both sheets whenever one of them has sorting applied? That would fix this up nicely. But that should probably be triggered in it's own Sub I think. And somehow call a macro to see if sorting has been applied. Not sure how that can be done though, don't think sorting has it's own event hander..

Edit: Found this, trapping autofilter event:
http://www.mrexcel.com/archive/Data/27590.html

However, I've tried so apply sorting previously in macro code and that didn't go too well. The columns in the sheets already have autofilter applied, it's just the sorting that I can't figure out how to do. I know I can use Selection, but I heard you should use .Selected as little as possible and rather target Ranges instead?

[watersev]

can you show me an example of what you'd like to have in addition to what the code does?

[Kenny Bones]

Well, if you look at the column D, it says "amount". That's for registering how many of that particular item that person has received. But the thing is, say if a person quits the company and that person needs to be removed from the Users list, what happens when you remove that row from the Users sheet? Well, the value from column D (amount) is not removed in the Summary sheet and appear on another user.

You can try it out yourself by commenting out the line .UsedRange.Offset(1).ClearContents from the code and then remove a row from the Users sheet. You'll notice the amount is not there.

[watersev]

why should I comment this line if it clears the old data and updates it with the new one according to users and articles shown?

[Kenny Bones]

Because the line clears the data from column D does it not? It's all blank in my spreadsheat atleast. The data in column D is only in the Summary sheet and nowhere else. So, when the macro is run, the data is cleared and that data isn't anywhere else. Meaning, it gets lost

[watersev]

please check attachments

1. v3(1) - sums duplicates
2. v3(2) - retains users and articles within those present on according sheets and adds new data