Same City Names in different States

[ethinix]

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.

[#2]

@ethinix 

=IF(COUNTIF($A$2:$A$962,A2)>1,A2&"-"&N2,A2)

This is the formula in cell Q2 which combines PHYSICAL CITY and PHYSICAL STATE if the PHYSICAL CITY is a duplicate. The formula is filled down column Q.

[#3]

@ethinix 

A Power Query option attached

let
    Source = Table,
    GroupedRows = Table.Group(Source, {"PHYSICAL CITY"},
        {"DATA", each
            Table.AddColumn(_, "PHYSICAL CITY-STATE", (o)=>
              if Table.RowCount(_) > 1 then Text.Combine({o[PHYSICAL CITY], o[PHYSICAL STATE]}, "-") else null
            ),
            type table
        }
    ),
    CombineTables = Table.Combine(GroupedRows[DATA])
in
    CombineTables

[#4]

Thank you so much. Appreciate your help.

[#5]

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.

[#6]

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

[#7]

@ethinix 

i hope it help with array trick