tina16marie
New Member
- Joined
- May 17, 2016
- Messages
- 2
Hello, I need help!
I need to create a formula that will help me validate data within a hierarchy rollup. As an example, I created a spreadsheet of cities with the State, Country, and Continent they map to. I'm looking to create a formula that returns a count if the state maps into more than one country.
Example of valid hierarchy - This example is valid because each State maps into 1 country.
Example of invalid hierarchy - This data is invalid because California maps into 2 countries (United States and Caribbeans) and New York maps into 2 countries (United States and Canada). I need a formula that will result a 2 in the The 1:1 mapping from State/Prov to Country column.
I need to create a formula that will help me validate data within a hierarchy rollup. As an example, I created a spreadsheet of cities with the State, Country, and Continent they map to. I'm looking to create a formula that returns a count if the state maps into more than one country.
Example of valid hierarchy - This example is valid because each State maps into 1 country.
| Validate for incorrect Hierarchy rollup | ||||||
| Continent | Country | State/Prov | City | 1:1 mapping from City to State/Prov | 1:1 mapping from State/Prov to Country | 1:1 mapping from Country to Continent |
| North America | United States | California | Los Angeles | 1 | 1 | 1 |
| North America | United States | California | San Diego | 1 | 1 | 1 |
| North America | United States | California | San Francisco | 1 | 1 | 1 |
| North America | United States | New York | New York | 1 | 1 | 1 |
| North America | United States | New York | Hempstead | 1 | 1 | 1 |
| North America | United States | New York | Albany | 1 | 1 | 1 |
| North America | Canada | Ontario | Toronto | 1 | 1 | 1 |
| North America | Canada | Ontario | Ottawa | 1 | 1 | 1 |
| North America | Canada | Quebec | Montreal | 1 | 1 | 1 |
| North America | Caribbean | Jamaica | Montego Bay | 1 | 1 | 1 |
Example of invalid hierarchy - This data is invalid because California maps into 2 countries (United States and Caribbeans) and New York maps into 2 countries (United States and Canada). I need a formula that will result a 2 in the The 1:1 mapping from State/Prov to Country column.
| Validate for incorrect Hierarchy rollup | ||||||
| Continent | Country | State/Prov | City | 1:1 mapping from City to State/Prov | 1:1 mapping from State/Prov to Country | 1:1 mapping from Country to Continent |
| North America | United States | California | Los Angeles | 1 | 2 | 1 |
| North America | United States | California | San Diego | 1 | 2 | 1 |
| North America | United States | California | San Francisco | 1 | 2 | 1 |
| North America | United States | New York | New York | 1 | 2 | 1 |
| North America | United States | New York | Hempstead | 1 | 2 | 1 |
| North America | United States | New York | Albany | 1 | 2 | 1 |
| North America | Canada | New York | Toronto | 1 | 2 | 1 |
| North America | Canada | Ontario | Ottawa | 1 | 1 | 1 |
| North America | Canada | Quebec | Montreal | 1 | 1 | 1 |
| North America | Caribbean | California | Montego Bay | 1 | 2 | 1 |
