Macro with Multiple Search Parameters for 2 Columns

Hi

I have 3 Columns in my worksheet, the first 2 columns is for Products Category & for Products Subcategory with a fixed category name for Categories and a fixed subcategory name for subcategories. Now on third column I want to create new categories according sometimes to the first column and sometimes to the second.

Here I have exactly what I want with the exact data that I want:

	A	B	C
1	Products Category	Products SubCategory	New Category (here the macro must create the new category)
2	Android Phones	(does not need to search for subcategory)	Electronics > Communications > Telephony > Mobile Phones
3	(does not need to search for category)	7 Inch Android Tablet PC	Electronics > Computers > Tablet Computers
4	(does not need to search for category)	8-9 Inch Android Tablet PC	Electronics > Computers > Tablet Computers
5	(does not need to search for category)	Cheap Android Tablets	Electronics > Computers > Tablet Computers
6	(does not need to search for category)	Tablet PC Accessories	Electronics > Computers > Computer Accessories > Tablet Computer Accessories
7	Android TV Media Players	(does not need to search for subcategory)	Electronics > Video > Video Players & Recorders > Home Media Players

Of course the macro must search entire columns because Ι have a lot of products that belongs to the same category or subcategory, so the category name exists in multiple cells.

This is an example file with real data in it: Example_new_categories.xlsx


The idea is a macro to work like this formula:

Please Login or Register  to view this content.

but with multiple search parameters etc for entire columns

UPDATE!!! I FOUND THE SOLUTION

This is the entire macro (just for the reference):

Ok This macro first deletes the unnecessary categories and subcategories from columns A & B
2) compines the 2 Columns in one and deletes the B column
3) it creates a new Temporary Sheet with the entire list of the old categories in column A and New Categories in column B
4) It uses this formula to the new categories column in my original sheet: =VLOOKUP(A1; Sheet7!$A:$B; 2; 0)
Final deletes the Temporary Sheet and the unneeded column A in my original Sheet.

Please Login or Register  to view this content.

OK I have manage to create a macro that first delete the unessessary categories and subcategories and then compines the 2 columns in one

Please Login or Register  to view this content.

Now with this formula I have managed to achieve my goal, which is to create a new category name according to the original categories:

Please Login or Register  to view this content.

The problem now is that the vba cannot store this formula because it's very long

What can I do for this?

Please help me.

UPDATE!!! I FOUND THE SOLUTION

This is the entire macro (just for the reference):

Ok This macro first deletes the unnecessary categories and subcategories from columns A & B
2) compines the 2 Columns in one and deletes the B column
3) it creates a new Temporary Sheet with the entire list of the old categories in column A and New Categories in column B
4) It uses this formula to the new categories column in my original sheet: =VLOOKUP(A1; Sheet7!$A:$B; 2; 0)
Final deletes the Temporary Sheet and the unneeded column A in my original Sheet.

Please Login or Register  to view this content.