We have An Excel spreadsheet of contacts with COMPANY, NAME, ADDRESS, CITY, ST, ZIP, REGION, PHONE, E-MAIL fields.
We have internally divided up the US into seven regions. I would like to know how to update the “REGION” field
automatically, as the respective “ST” fields are entered. For example; as IL is typed in, the corresponding REGION cell fills
with “Great Lakes”, CT corresponds to “Mid Atlantic”, CA with “Pacific” – so on and so forth with all 52 states.
As the spreadsheet becomes bigger, what would be the quickest way to weed out duplicates? I would assume that a single
query on the “ADDRESS” field would suffice, assuming the file is properly sorted (by ST, then ZIP, then ADDRESS).
(Hopefully this would help some of the "duplicate entry" questions). What we do now is to sort as above, then add a field
next to ADDRESS with this formula in the first cell; =IF(X1=X2,1," ") with X being the corresponding cells in question. This
formula is then copied all the way down. Wherever a duplicate address cell is found, the number "1" appears, else the cell is
blank. We then create another blank field next to this "formula" field, copy and "paste special - values" the formula field
to this new field, then sort the entire spreadsheet by this newest field, which lumps all the 1s on top. Then manually delete
Don't know if that was understandable, or perhaps even too amateurish.
Any pointers would
be greatly appreciated, including how to manage this spreadsheet more effectively.
Thank you very, very much.