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

Sub jmay()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Item(Trim(Left(Cl.Value, 3))) = .Item(Trim(Left(Cl.Value, 3))) + Cl.Offset(, 1).Value
      Next Cl
      Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

[jmay91]

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

Sub jmay()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Item(Trim(Left(Cl.Value, 3))) = .Item(Trim(Left(Cl.Value, 3))) + Cl.Offset(, 1).Value
      Next Cl
      Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

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

[alansidman]

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumnbyDelimiterSpace = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    RemovedSecondaryColumn = Table.RemoveColumns(SplitColumnbyDelimiterSpace,{"Column1.2"}),
    SumByPrimary = Table.Group(RemovedSecondaryColumn, {"Column1.1"}, {{"Total", each List.Sum([Column2]), type number}})
in
    SumByPrimary

[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?

Sub threedigitpostcode()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Item(Trim(Left(Cl.Value, 4))) = .Item(Trim(Left(Cl.Value, 4))) + Cl.Offset(, 1).Value
      Next Cl
      Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

[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] [/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

Sub threedigitpostcode()
   Dim Cl As Range
   Dim ValU As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         ValU = Split(Cl.Value)(0)
         .Item(ValU) = .Item(ValU) + Cl.Offset(, 1).Value
      Next Cl
      Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub