Hi Folks,
I hope you all are doing good.
I badly need to design a macro to automate a process which i am doing manually and taking hell lot of time.
I have attached an excel sheet.
Please find below the pointers that i need to do in it.
- open the attached file.
- Go to sheet3, where you will find a table at the top with headers code, name and country names etc(A1:J11)
- Whereever there is "yes" mentioned in that table i want country name and code(mentioned in column A). i want to list that country name and code from row A14 onwards. Challenge here is, i want country name as many times as there is "yes" in above table.
- To make it simple to understand this time i have copied such data manually from cell A14 onwards. E.g. if you look at United Kingdom, there are 2 yes in above table, so i took United kingdom twice from cell A14 and infront of that i also took code of those 2 yes from above table(A7 & A10)
Here ends the first part of the macro.
lets move to 2nd part.
- In C & D column of sheet3 you will find cells with Filter Criteria headers. I have formulated that. No need to touch them. What I want to do is, use them as a filter criteria in Sheet1 column P
- But the filter should be for Deselecting the values this time.
- For example, in sheet3, cell C14 there is value "UK:06:" , so in sheet1 column P I want to apply filter and deselect rows which have value "UK:06:". and in same way it should deselect all rows which have values in C & D column filter criteria
- Ideally Code should automatically find unique vales from filter criteria, and then apply filter in sheet 1, because all the values are repeating again and again.
- Once the filter is applied, sheet 1 should have all rows except the rows which does not have values mentioned filter criteria in sheet3(like UK:06:, UK:07:, NE:06:. NE:07: etc)
It would be great if you can assist me on this.
Thanks,