How to remove duplicate postal codes

[akirk]

I have a spreadsheet with about 5000 postal codes. I don’t need this many because I’m using this to create a map showing approximate delivery locations. So I would like to remove the postal codes which have the same first 3 characters.

For example, here is a small list:

A0A 2J0
A0A 2M0
A0A 3C0
A0A 4J0

So, I would like to keep the first postal code, but remove the other ones that start with A0A.

I don’t have MS Access, but I have Excel 2013.

Any help is appreciated.

Thanks,

Ak

[molona]

What I would do? In a second column, I would use the function LEFT to keep the first three characters. Then I would copy the result and do a paste special to paste the values and then I would use the “remove duplicates” button from the resulting column. The button can be found under the Data tab

I don’t know if that helps because that would leave you with 3 characters… Now, if you really need to keep the first postal code, with the six characters, then it’s a bit different

Again, I would do this in a second column but first, I would make sure that the column with the data is alphabetically ordedered.

Then, in the second column I would use a combination of funtion to compare the first 3 characters from one cell with the one above. If there’s a change, then I would right the postal code. If it is equal, I would write nothing.
Once done, I, again, would select the column, paste special (as values) and then order the second column to get rid of all the empty cells in the middle.

As an example, let’s say that all my postal codes are in column A and that I write my formula in column B.
B1, I would write it by hand.
From B2 onwards, the formula would be IF(LEFT(A2,3))LEFT(A1,3);“”;A2)
Then fill to the bottom (till column A has no more postal codes).
Select column B, copy and paste special as values (so I don’t have formulas anymore). With column B still selected, sort the column so all values are at top.

[akirk]

Thank you for your response.

here’s what I did:

I decided that I only needed the first 2 characters of the postal code (because I didn’t need that much detail in my map), so I copied the first 2 characters of the postal code from the left column, to the right column using this (filling down of course):

=LEFT(A1,2)

Then, I did a google search for a function to remove duplicates (there was a lot). Then, in order to fill out the postal codes, I concatenated with this:

=CONCATENATE(A1, “A 1AA”)

So all my postal codes were like this: M8A 1AA; M9A 1AA; M7A 1AA etc.

And I was left with 141 rows of beautiful simple postal codes which I could now import into BatchGeo to show markers on a map.

Thanks!

[molona]

Glad that I could help. Didn’t understand why you use Google to removed the duplicates (Excel does already include a button with that function) but it worked and that’s all that counts