quemuenchatocha
Board Regular
- Joined
- Aug 4, 2021
- Messages
- 50
- Office Version
- 365
- 2019
- Platform
- Windows
Cordial greetings
I have two concerns and they are as follows.
The first one is, in column A are registered 6 States with their respective cities, the name of each State is in capital letters, now, I need to extract in a separate column (can be for example column L) only the names corresponding to the 6 States, use the function FILTER, ISBLANK and EXACT(UPPER), but I can not get the expected results. Is it possible to obtain this list of only the States in a separate column?
The second query has to do with the longitude and latitude values for each of the cities. For cells H3 and H7 are the values of the States while in cells H4 and H8 are the cities for each of the states recorded in a data list.
For example, in cells I4 and J4 I get the data corresponding to the longitude and latitude respectively for the State and city chosen in cells H3 and H4, the problem lies for those cities in different States that have the same name, for example, if I want to do this same calculation, but this time for the city of Greenville in the State of North Carolina (or Texas, Tennessee, South Carolina) in cells I8 and J8, I get the same results obtained for the city of Greenville but in the State of Illinois.
Is there any formula where it is possible to calculate the corresponding values for each of the cities in your respective state?
Thank you very much for your attention and help with any hints on a formula that can be used to obtain the results!
I have two concerns and they are as follows.
The first one is, in column A are registered 6 States with their respective cities, the name of each State is in capital letters, now, I need to extract in a separate column (can be for example column L) only the names corresponding to the 6 States, use the function FILTER, ISBLANK and EXACT(UPPER), but I can not get the expected results. Is it possible to obtain this list of only the States in a separate column?
The second query has to do with the longitude and latitude values for each of the cities. For cells H3 and H7 are the values of the States while in cells H4 and H8 are the cities for each of the states recorded in a data list.
For example, in cells I4 and J4 I get the data corresponding to the longitude and latitude respectively for the State and city chosen in cells H3 and H4, the problem lies for those cities in different States that have the same name, for example, if I want to do this same calculation, but this time for the city of Greenville in the State of North Carolina (or Texas, Tennessee, South Carolina) in cells I8 and J8, I get the same results obtained for the city of Greenville but in the State of Illinois.
Is there any formula where it is possible to calculate the corresponding values for each of the cities in your respective state?
Libro1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ALABAMA | LATITUDE | LONGITUDE | |||||||||||
2 | Alexander City | 32,95 | 85,95 | Starting Location | LAT | LON | States | |||||||
3 | Anniston | 33,58 | 85,85 | State | ILLINOIS | ALABAMA | ||||||||
4 | Auburn | 32,60 | 85,50 | City | Greenville | 38,8833333 | 89,4 | ILLINOIS | ||||||
5 | Birmingham | 33,57 | 86,75 | NORTH CAROLINA | ||||||||||
6 | Decatur | 34,62 | 86,98 | Ending Location | SOUTH CAROLINA | |||||||||
7 | Dothan | 31,32 | 85,45 | State | NORTH CAROLINA | TENNESSEE | ||||||||
8 | Florence | 34,80 | 87,67 | City | Greenville | 38,8833333 | 89,4 | TEXAS | ||||||
9 | Gadsden | 34,02 | 86,00 | |||||||||||
10 | Huntsville | 34,70 | 86,58 | |||||||||||
11 | Mobile | 30,68 | 88,25 | |||||||||||
12 | Mobile Co | 30,67 | 88,25 | |||||||||||
13 | Montgomery | 32,38 | 86,37 | |||||||||||
14 | Selma-Craig AFB | 32,33 | 87,98 | |||||||||||
15 | Talladega | 33,45 | 86,1 | |||||||||||
16 | Tuscaloosa | 33,217 | 87,617 | |||||||||||
17 | ||||||||||||||
18 | ILLINOIS | LATITUDE | LONGITUDE | |||||||||||
19 | Aurora | 41,75 | 88,33 | |||||||||||
20 | Belleville, Scott AFB | 38,55 | 89,85 | |||||||||||
21 | Bloomington | 40,48 | 88,95 | |||||||||||
22 | Carbondale | 37,78 | 89,25 | |||||||||||
23 | Champaign/Urbana | 40,03 | 88,28 | |||||||||||
24 | Chicago, Midway | 41,78 | 87,75 | |||||||||||
25 | Chicago, O'Hare | 41,98 | 87,90 | |||||||||||
26 | Chicago Co | 41,88 | 87,63 | |||||||||||
27 | Danville | 40,20 | 87,60 | |||||||||||
28 | Decatur | 39,83 | 88,87 | |||||||||||
29 | Dixon | 41,83 | 89,48 | |||||||||||
30 | Elgin | 42,03 | 88,27 | |||||||||||
31 | Freeport | 42,30 | 89,62 | |||||||||||
32 | Galesburg | 40,93 | 90,43 | |||||||||||
33 | Greenville | 38,88 | 89,40 | |||||||||||
34 | Joliet | 41,52 | 88,17 | |||||||||||
35 | Kankakee | 41,08 | 87,92 | |||||||||||
36 | La Salle/Peru | 41,32 | 89,10 | |||||||||||
37 | Macomb | 40,47 | 90,67 | |||||||||||
38 | Moline | 41,45 | 90,52 | |||||||||||
39 | Mt Vernon | 38,32 | 88,87 | |||||||||||
40 | Peoria | 40,67 | 89,68 | |||||||||||
41 | Quincy | 39,95 | 91,20 | |||||||||||
42 | Rantoul, Chanute AFB | 40,30 | 88,13 | |||||||||||
43 | Rockford | 42,35 | 89,05 | |||||||||||
44 | Springfield | 39,83 | 89,67 | |||||||||||
45 | Waukegan | 42,35 | 87,88 | |||||||||||
46 | ||||||||||||||
47 | NORTH CAROLINA | LATITUDE | LONGITUDE | |||||||||||
48 | Asheville | 35,43 | 82,53 | |||||||||||
49 | Charlotte | 35,22 | 80,93 | |||||||||||
50 | Durham | 35,87 | 78,78 | |||||||||||
51 | Elizabeth City | 36,27 | 76,18 | |||||||||||
52 | Fayetteville, Pope AFB | 35,17 | 79,02 | |||||||||||
53 | Goldsboro,Seymour-Johnson | 35,33 | 77,97 | |||||||||||
54 | Greensboro | 36,08 | 79,95 | |||||||||||
55 | Greenville | 35,62 | 77,42 | |||||||||||
56 | Henderson | 36,37 | 78,42 | |||||||||||
57 | Hickory | 35,75 | 81,38 | |||||||||||
58 | Jacksonville | 34,83 | 77,62 | |||||||||||
59 | Lumberton | 34,62 | 79,07 | |||||||||||
60 | New Bern | 35,08 | 77,05 | |||||||||||
61 | Raleigh/Durham | 35,87 | 78,78 | |||||||||||
62 | Rocky Mount | 35,97 | 77,80 | |||||||||||
63 | Wilmington | 34,27 | 77,92 | |||||||||||
64 | Winston-Salem | 36,13 | 80,22 | |||||||||||
65 | ||||||||||||||
66 | SOUTH CAROLINA | LATITUDE | LONGITUDE | |||||||||||
67 | Anderson | 34,50 | 82,72 | |||||||||||
68 | Charleston AFB | 32,90 | 80,03 | |||||||||||
69 | Charleston Co | 32,90 | 79,97 | |||||||||||
70 | Columbia | 33,95 | 81,12 | |||||||||||
71 | Florence | 34,18 | 79,72 | |||||||||||
72 | Georgetown | 33,38 | 79,28 | |||||||||||
73 | Greenville | 34,90 | 82,22 | |||||||||||
74 | Greenwood | 34,17 | 82,12 | |||||||||||
75 | Orangeburg | 33,50 | 80,87 | |||||||||||
76 | Rock Hill | 34,98 | 80,97 | |||||||||||
77 | Spartanburg | 34,97 | 82,00 | |||||||||||
78 | Sumter, Shaw AFB | 33,90 | 80,37 | |||||||||||
79 | ||||||||||||||
80 | TENNESSEE | LATITUDE | LONGITUDE | |||||||||||
81 | Athens | 35,43 | 84,58 | |||||||||||
82 | Bristol-Tri City | 36,48 | 82,40 | |||||||||||
83 | Chattanooga | 35,03 | 85,20 | |||||||||||
84 | Clarksville | 36,55 | 87,37 | |||||||||||
85 | Columbia | 35,63 | 87,03 | |||||||||||
86 | Dyersburg | 36,02 | 89,40 | |||||||||||
87 | Greenville | 36,07 | 82,83 | |||||||||||
88 | Jackson | 35,60 | 88,92 | |||||||||||
89 | Knoxville | 35,82 | 83,98 | |||||||||||
90 | Memphis | 35,05 | 90,00 | |||||||||||
91 | Murfreesboro | 34,92 | 86,47 | |||||||||||
92 | Nashville | 36,12 | 86,68 | |||||||||||
93 | Tullahoma | 35,38 | 86,08 | |||||||||||
94 | ||||||||||||||
95 | TEXAS | LATITUDE | LONGITUDE | |||||||||||
96 | Abilene | 32,42 | 99,68 | |||||||||||
97 | Alice | 27,73 | 98,03 | |||||||||||
98 | Amarillo | 35,23 | 100,70 | |||||||||||
99 | Austin | 30,30 | 97,70 | |||||||||||
100 | Bay City | 29,00 | 95,97 | |||||||||||
101 | Beaumont | 29,95 | 94,02 | |||||||||||
102 | Beeville | 28,37 | 97,67 | |||||||||||
103 | Big Spring | 32,30 | 101,45 | |||||||||||
104 | Brownsville | 25,90 | 97,43 | |||||||||||
105 | Brownwood | 31,80 | 98,95 | |||||||||||
106 | Bryan | 30,67 | 96,55 | |||||||||||
107 | Corpus Christi | 27,77 | 97,50 | |||||||||||
108 | Corsicana | 32,08 | 96,47 | |||||||||||
109 | Dallas | 32,85 | 96,85 | |||||||||||
110 | Del Rio, Laughlin AFB | 29,37 | 100,78 | |||||||||||
111 | Denton | 33,20 | 97,10 | |||||||||||
112 | Eagle Pass | 28,87 | 100,53 | |||||||||||
113 | El Paso | 31,80 | 106,40 | |||||||||||
114 | Fort Worth | 32,83 | 97,05 | |||||||||||
115 | Galveston | 29,30 | 94,80 | |||||||||||
116 | Greenville | 33,07 | 96,05 | |||||||||||
117 | Harlingen | 26,23 | 97,65 | |||||||||||
118 | Houston | 29,97 | 95,35 | |||||||||||
119 | Houston Co | 29,98 | 95,37 | |||||||||||
120 | Huntsville | 30,72 | 95,55 | |||||||||||
121 | Killeen, Robert Gray AAF | 31,08 | 97,68 | |||||||||||
122 | Lamesa | 32,70 | 101,93 | |||||||||||
123 | Laredo AFB | 27,53 | 99,45 | |||||||||||
124 | Longview | 32,47 | 94,73 | |||||||||||
125 | Lubbock | 33,65 | 101,82 | |||||||||||
126 | Lufkin | 31,42 | 94,80 | |||||||||||
127 | Mcallen | 26,20 | 98,22 | |||||||||||
128 | Midland | 31,95 | 102,18 | |||||||||||
129 | Mineral Wells | 32,78 | 98,07 | |||||||||||
130 | Palestine Co | 31,78 | 95,63 | |||||||||||
131 | Pampa | 35,53 | 100,98 | |||||||||||
132 | Pecos | 31,42 | 103,50 | |||||||||||
133 | Plainview | 34,18 | 101,70 | |||||||||||
134 | Port Arthur | 29,95 | 94,02 | |||||||||||
135 | Goodfellow AFB | 31,43 | 100,40 | |||||||||||
136 | San Antonio | 29,53 | 98,47 | |||||||||||
137 | Sherman, Perrin AFB | 33,72 | 96,67 | |||||||||||
138 | Snyder | 32,72 | 100,92 | |||||||||||
139 | Temple | 31,10 | 97,35 | |||||||||||
140 | Tyler | 32,35 | 95,27 | |||||||||||
141 | Vernon | 34,17 | 99,30 | |||||||||||
142 | Victoria | 28,85 | 96,92 | |||||||||||
143 | Waco | 31,62 | 97,22 | |||||||||||
144 | Wichita Falls | 33,97 | 98,48 | |||||||||||
Hoja1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I4 | I4 | =INDEX(B:B,MATCH($H$4,A:A,0)) |
J4 | J4 | =INDEX(C:C,MATCH($H$4,A:A,0)) |
I8 | I8 | =INDEX(B:B,MATCH($H$8,A:A,0)) |
J8 | J8 | =INDEX(C:C,MATCH($H$8,A:A,0)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
ALABAMA | =Hoja1!$A$2:$A$16 | I8:J8, I4:J4 |
ILLINOIS | =Hoja1!$A$19:$A$45 | I8:J8, I4:J4 |
NORTH_CAROLINA | =Hoja1!$A$48:$A$64 | I8:J8, I4:J4 |
SOUTH_CAROLINA | =Hoja1!$A$67:$A$78 | I8:J8, I4:J4 |
TENNESSEE | =Hoja1!$A$81:$A$93 | I8:J8, I4:J4 |
TEXAS | =Hoja1!$A$96:$A$144 | I8:J8, I4:J4 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H3 | List | =$L$3:$L$8 |
H4 | List | =INDIRECTO(SUSTITUIR($H$3;" ";"_")) |
H7 | List | =$L$3:$L$8 |
H8 | List | =INDIRECTO(SUSTITUIR($H$7;" ";"_")) |
Thank you very much for your attention and help with any hints on a formula that can be used to obtain the results!