Postcode grouping

[jmay91]

Hello all,

I have a data set which lists 6 digit postcodes & a number of houses in each of them.

I would like to combine these postcodes into their respective 3 digit area to get a cumulative amount of houses in each.

E.G
A1 ------------------------------A2
BR1 11NF-----------------------1
BR1 1AA-------------------------1
BR1 1AB------------------------ 2

I would want to show
A1 ------------------------------A2
BR1------------------------------4

Is this possible and could anyone kindly help me?

many thanks

[GR00007]

You could use a Pivot table either creating a calculated field for Left(postalcode,3) or creating a new column to do the same thing, then sum the house quantity.

[Fluff13]

Another option, assuming your data is in columns A & B

Please Login or Register  to view this content.

[alansidman]

A Power Query Solution. Here is the Mcode. Attached is a demo file

Please Login or Register  to view this content.

[jmay91]

Another option, assuming your data is in columns A & B

Please Login or Register  to view this content.

Thank you for all your help folks, very kind of you. I went with this option as I took it as a learning opportunity to expand my Macro knowledge.
Very helpful,
have a good day!
J

[Fluff13]

You're welcome & thanks for the feedback

[jmay91]

Hello all,
Back again with a Q on the macro. I am attempting to get the postcodes to show 2, 3 & 4 digits now e.g
E1
SW2
SW12

I noticed before that 2 & 3 digits showed, so i changed the formula below (bolded what I changed) and it runs fine but then nothing happens. Would anyone know why?

Please Login or Register  to view this content.

[alansidman]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Fluff13]

That modification works fine for me.
If you are just trying to get the first set of characters, how about

Please Login or Register  to view this content.