I have a list of zip codes with corresponding territories but what I need is a territory and the corresponding min max zip code range for all territoies and zip values. For example:
Zip | Terr |
1 | T1 |
2 | T1 |
3 | T1 |
4 | T2 |
5 | T1 |
6 | T1 |
7 | T1 |
would return
Terr | Zip Start | Zip End |
T1 | 1 | 3 |
T2 | 4 | 4 |
T1 | 5 | 7 |
Thought Tile would work but I played around with a few settings and had no luck. I was able to use a multi row formula to tell me if the zip code had the same territory as the row above it which I think is on the right track but I'm pretty sure there may be a better way.
Thanks!
Solved! Go to Solution.
@PRossi It seems to me like the summarize tool would accomplish that. I've attached a quick sample.
If I'm understanding this correctly, there is a space in front of the second set of " T1" entries, which allows the simple summarize to work correctly. But, if that space is errant and should be removed, then the T1's will all group together and we'll only get two final results, rather than three. If the question is how to get three results, the fix then would be to build a "rank" using MultiRow Formula, and group on that rather than Territory. (See attached).
(edit: modified workflow to join back to original).
Thank you, that is exactly what I was looking for, yes the space before T1 was in error.