Modifying the data in Sheet2, and the reference to it in columns E and F of Sheet1 should allow you to reuse this for any county in your state, as long as they use the same system with the PID. The formula for cell F2 is =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,3), except that it's the third column, this works the same as the formula in cell E2. This is probably better, since when you type numbers Excel wants to store them as numbers, not text, unless you force the issue with quotes or explicit cell formatting. Note, this is the one point to be carefull! The values in the indexing column of Sheet2 must be numbers, not text. The formula for cell E2 is =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,2), to find the value (numeric) of cell B2 in column A of Sheet2 and return the value of the 2nd column in the same row. (8,000 Gunter's links would work, I guess, if you're in a place that still uses that system, or even inches fits in 5 digits.) I'm guessing that the remaining 10 digits are in two groups of 5 for sectional divisions east and north of the sectional corners. Using the last formula as a guide you should be able to extract any other part of the PID that is positionally predictable. The formula for cell D2 is =MID(A2,4,2), to extract the section number from the PID. The formula for cell C2 is =MID(A2,3,1), to extract the map sheet from the PID. The formula for cell B2 is =LEFT(A2,2), to extract the survey township number from the PID. Using your sample data for PID 0241800000001000 I created this as Sheet1: This is the "map" I used for the example.Ĭonverting that map into data for your Sheet2 generates the following sheet: (Since you have the maps, and know the area, you can adjust my example to suit.) I made this hypothetical map cross both a baseline and a meridian so that you would see its application in that eventuality. Since you said that the current county has 20 survey townships, I created a hypothetical "map" of the country, showing it overlay of 20 survey townships onto a PLSS grid. Thus, all 9 sheets of survey township 02 correspond to the old 9 sheets of Township 04S Range 06W (or 06E). Reading the description again, looking for answers to my earlier questions, I have concluded that each survey township not only falls within the grid of a single township and range square in the PLSS, but that it also matches it, except for the nomenclature. Would love to help more, but we need more information. But again, I didn't have an example of the table. In which the following formula should get you what you want: =INDEX(Sheet2!$B$1:$B$19,MATCH(LEFT(B2,3),Sheet2!$A$1:$A$19,0))Īnd yes, tables can be your friend here (especially on Sheet2). Now, put the result you want for those values in column B. Instead of having these "groups" in separate columns, try putting all the possible values of the PID in column A (still Sheet2). Or it fails with an error.Īside from winning the award for "Most Confusing Problem Statement" (please read comments from I think you want to match the PID/township values to a list in Sheet2. The output keeps either telling me "FALSE" or !VALUE#. B1:B9 is referencing the mapsheet list for survey township 02 (021-029).A1:A9 is referencing the mapsheet list for survey township (011-019).The survey township range (021-029) and the PLSS township (04S) output are my variables, which I will alter per county. The formula needs to refer to the list on sheet 2 and if the first 3 characters of the PID for survey township 2 are 021-029, the output for the township cell should be (04S) for township 4 South. The particular county has 20 survey townships. I created a reference list in sheet2 that lists every map sheet in the county (011-209). I'm now working on the township and range. Nine map sheets with 4 sections per sheet is the 36. There are 36 sections in a "survey township". However, each "survey township" falls within the grid of a single township and range square in the PLSS.Įach survey township is made up of 9 "map sheets" from the old days of milar map sheets which held 4 sections per map. However the "survey township" should not be confused with the township and range of the PLSS. The PID begins with the 3 digits of the survey township followed by the two section number digits. I'm running Office 2010 and I'm trying to build a function in Excel that derives the section, township and range from the 16 digit parcel identification number (PID) into individual columns. I am a GIS specialist for a cadastral mapping company.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |