Hi Brent,
Attached is the previous Macro script you recently prepared for us. As a reminder, what the script does is running 30 hotels chain names with a list of cities.
In the previous Macro we assumed that all 30 hotel chains were present in each of the cities. Naturally they aren't and we would like the script to check for that first. So:
1. In the attached file (the same Macro file you made) I added a tab named "All Hotels". In this tab there's a list of 14771 hotels. I added to each chain it's label: C01 till C30.
2. For this task we need to use two parameters: CityID and ChainCode. The ChainCode appears in Column-A and CityId in Column-G. So, I need a script that counts how many times a specific hotel chain appears in a given city, and only create an ad group (and therefore a line in the Result tab) if the hotel is present in that city. If it's not, it shouldn't process it and therefore it should not appear in the result tab. To do that the script should count how many times the ChainCode value appears in combination with a single CityID.
So for example, if I use the Excel filter for Barcelona (filter by using the CityID = 201), I find it has 2 "Abba" hotels (ChainCode=C01), zero "All Seasons" hotels (ChainCode=C02), 3 "Barcelo" hotels (ChainCode=C03), and so on... So in this example the RESULT tab should not produce a line for "All Seasons" hotel in Barcelona, because it doesn't exist. Does it make sense?
3. Next, if the chain has more than one hotel in a city, I would like to have the option to mention the number of hotels in the Headline. For that I need the script to replace the word "Number" in the Config tab with the number of hotels found by the script. So for example, if the script counted 33 Holiday Inn in London (ChainCode=C09), I can create the headline: "33 Holiday Inn in London".
Hi Oz
Okay... Here's my understanding: The new macro needs to count how many of a particular chain's hotels are in a city; as the macro processes the RESULTS worksheet, only output a line if there is 1 or more hotels for the chain in that city.
In CONFIG, replace the text "Number" in cell A5 with the number of hotels in that city if there are more than 1; otherwise just remove the text "Number" (for only 1 hotel in that city)
Am I right?
This won't take long to do; 1 day or sooner