Determine a techs general area.

[oneyejack]

I need to create a way to determine a techs general area, i.e. Area 1,2,3 or 4.

Info that I export from my data base appears as follows:

Tech Job Id Type/Units Service Address Zip Code State
245603 HT/ 12 xxx 553696741 UA
0511 242808 VD/ 5 xxx 553035928 AS
0511 245625 SR/ 8 xxx 553034716 AS
0511 229103 RD/ 26 xxx 553034503 AS
0511 244546 HT/ 12 xxx 553047628 AS
0513 239523 VD/ 5 xxx 554283378 AS
0513 241145 SX/ 19 xxx 554284444 IS
0513 238191 DN/ 6 xxx 554285508 AS
0513 241976 TC/ 12 xxx 554283315 AS
0514 227015 PV/ 74 xxx 553161919 IS
0517 239062 TR/ 42 xxx 554292829 IS
0517 241907 TC/ 12 xxx 553036861 AS
0517 245658 TV/ 12 xxx 553033975 AS
0519 245742 TC/ 12 xxx 554291153 AS
0522 240510 TC/ 12 xxx 554461763 AS
0525 234762 VD/ 5 xxx 554331278 IS
0525 240486 SR/ 8 xxx 554482597 AS
0525 241942 TC/ 12 xxx 553041552 AS
0525 236469 UP/ 7 xxx 554482011 AS
0526 245689 TC/ 12 xxx 554441215 IS

I believe there are about 7 zip codes per area. The tech number and zip codes are actually exported as text. In order to make the techs and zip codes easy to read I use the MID function to change them. For example I change '0526 and '554441215 to 526 and 55444. Once I have changed the exported data to the following:

Tech Zip Codes



55369
511 55303
511 55303
511 55303
511 55304
513 55428
513 55428
513 55428
513 55428
514 55316
517 55429
517 55303
517 55303
519 55429
522 55446
525 55433
525 55448
525 55304
525 55448
526 55444

I want to be able to determine an area (1,2,3 or 4) for each zip code and the area where the majority of each techs jobs are. The end result should be as follows

511 1
513 3
514 3
517 1
519 3
522 4
525 1
526 3

I'm sure there are many ways to do this, looking for some guidance.
Thanks.

[oneyejack]

I figured just about everything out, what I need now is simple, just not sure how to do it.

A B C D E
1 area 1 area 2 area 3 area 4
2 5 0 1 0 ?

Where the "?" is I want to have a result of 1,2,3 or 4 depending on which cell has the larger number. From the above example A2 has the largest number,5, so E2 should display a "1" which will represent area 1.

A B C D E
1 area 1 area 2 area 3 area 4
2 0 4 2 2 ?

In this last example B2 has the largest number so E2 would display a "2", for area 2.

How could I achieve this?

[Paul]

Hi, try this formula in E2 (and can be filled downward if you have multiple rows):

Please Login or Register  to view this content.

[Paul]

If you just want the number to appear, not "area 1" or "area 2", modify my formula slightly like so:

Please Login or Register  to view this content.