I am trying to build a Macro that checks to the see the location in the incoming data, which is provided to us in State abbreviations or foreign country. My idea is to build a macro, that will check the data and compare it to a list of all 51 state abbrevations, if the state abbreviations does not show up in the data, then I want to classify it as a international. The example is below
Incoming Sample Data:
State/Location |
CA |
CA |
CA |
CA |
MA |
MA |
MA |
FG |
NH |
NH |
NH |
NH |
India |
Mexico |
MA |
Output data:
State/Location | Location FORMATTED |
CA | CA |
CA | CA |
CA | CA |
CA | CA |
MA | MA |
MA | MA |
MA | MA |
FG | FG |
NH | NH |
NH | NH |
NH | NH |
NH | NH |
India | International |
Mexico | International |
MA | MA |
As of right now I developed a Macro, that has a list of all the 51 states as a control parameter, I then use a action tool to update the State in the Action tool.
When I run the Macro, I notice that it filters correctly for the states, but does not output any data that is classified as "International"
Any help on this would be helpful.
I have attached all the US states Abbreviation
Solved! Go to Solution.
Hi,
For an example like this I would go with a different macro design, using Find/Replace. I've attached an example of this macro.
One of the cool features of Find/Replace is that it can append a field when it finds a match. So I set this up to compare your list of all states against any incoming data, and then used a formula to replace null values with the word International.
You could change the input of all states to be a secondary macro input as well if you want the user to be able to select their own custom list of valid values.
Hope this helps!
Thank you so much! this helps alot, especially allowing the user to have the flexibility to have their own inputs.
Appreciate it.