Same City Names in different States

Question

I have attached a sample file. Column A contains the cities. Some of them have same city names but their state names are different(column M). I want to add State abbreviation (Column N) to only those cities which are duplicates. For Example if AKRON city is present in Colorado and Indiana, I want Akron Colorado to be Named as AKRON-CO and Akron Indiana to be named as AKRON-IN.

Any help would be greatly appreciated.

oliverscheurich · Answer

ethinix&nbsp;=IF(COUNTIF($A$2:$A$962,A2)&gt;1,A2&amp;"-"&amp;N2,A2)&nbsp;This is the formula in cell Q2 which combines PHYSICAL CITY and&nbsp;PHYSICAL STATE if the&nbsp;PHYSICAL CITY is a duplicate. The formula is filled down column Q.

lorenzo · Answer

ethinix&nbsp;&nbsp;A Power Query option attachedlet
    Source = Table,
    GroupedRows = Table.Group(Source, {"PHYSICAL CITY"},
        {"DATA", each
            Table.AddColumn(_, "PHYSICAL CITY-STATE", (o)=&gt;
              if Table.RowCount(_) &gt; 1 then Text.Combine({o[PHYSICAL CITY], o[PHYSICAL STATE]}, "-") else null
            ),
            type table
        }
    ),
    CombineTables = Table.Combine(GroupedRows[DATA])
in
    CombineTables

ethinix · Answer

Thank you so much. Appreciate your help.

ethinix · Answer

Thank you so much. Appreciate your help.
Also kindly let me know if there is anyway, the sheet can fetch Lattitude, Longitude, County and County FIPS from any external source like Google Maps. The file contains the same Lat, Long, County and County info for all the duplicate cities irrespective of the states they are in. Just wondering if this is possible, rather than editing them manually.

ethinix · Answer

I was able to figure this out using Google's Geo Coding. Please ignore the above message. Thanks

Forum Discussion

Same City Names in different States

6 Replies

Resources