Dear community,

I am trying to amend a large set of data and would be eternally grateful for any help in creating a macro that will complete this task automatically.

The task is as follows:

I have two columns of data, one is called “Category” the other “Sub Category”:


Category Sub Category
Bag Other
Bag Plastic
Drum Plastic Large
Bag Other
Drum Other
Box Other
Drum Steel
Bag Plastic
Bag Plastic
Box Plastic
Drum Steel
Etc.

Now what I need to do is clean this data up, so that any duplicates across categories are specified, in order to be able to differentiate the subcategories.

i.e. after the macro has run it should look something like this:

Category Sub Category
Bag Other - Bag
Bag Plastic - Bag
Drum Plastic Large
Bag Other - Bag
Drum Other - Drum
Box Other - Box
Drum Steel
Bag Plastic - Bag
Bag Plastic - Bag
Box Plastic - Box
Drum Steel

I know the easiest would be to create a new column with a formula such as =A1&" - "&B1 however that would too unspecific as it will create a different subcategory for all items and not just the duplicates. I only need to amend the duplicates!

It would have to be something along the lines of:

If the subcategory of a specific category exists also as in another category add the appropriate "- Category" to each Subcategory item.

Unfortunately my knowledge of VBA is too limited to accomplish this.

Any help would be much appreciated!

Thank you