Sum the cells of one column if the content of cells in three other columns are duplicates.

[GAJITCS]

I have a table of data as below:

	A	B	C	D	E	F
1	Company	Add1	Town	Postcode	Region	Pieces
2	ABC	1 Alpha Street	Hull	HU1 2PP	1	2
3	DEF	23 Delta Road	Leeds	LE32 2UH	2	3
4	ABC	3 Bravo Close	Hull	HU1 4HY	1	1
5	DEF	4 Echo Lane	Derby	DE3 8GF	3	4
6	GHI	8 Foxtrot Avenue	Halifax	HA1 2RR	4	3
7	ABC	1 Alpha Street	Hull	HU1 2PP	1	2
8	GHI	8 Foxtrot Avenue	Halifax	HA1 2RR	4	6
9	DEF	23 Delta Road	Leeds	LE32 2UH	2	4
10	ABC	3 Bravo Close	Corby	CO17 6WS	5	3
11	GHI	8 Foxtrot Avenue	Hessle	HU3 3WQ	1	4

<tbody>
</tbody>

I need to produce a table from this where the number of Pieces in Column F are totalled where the Company, Add1 and Post Code values in Columns A,B and D are exactly the same.
The following Rows would be considered as matching.
2 and 7
3 and 9
6 and 8

So the resultant data would need to look like:

	A	B	C	D	E	F
1	Company	Add1	Town	Postcode	Region	Pieces
2	ABC	1 Alpha Street	Hull	HU1 2PP	1	4
3	DEF	23 Delta Road	Leeds	LE32 2UH	2	7
4	ABC	3 Bravo Close	Hull	HU1 4HY	1	1
5	DEF	4 Echo Lane	Derby	DE3 8GF	3	4
6	GHI	8 Foxtrot Avenue	Halifax	HA1 2RR	4	9
7	ABC	Bravo Close	Corby	CO17 6WS	5	3
8	GHI	8 Foxtrot Avenue	Hessle	HU3 3WQ	1	4

<tbody>
</tbody>

If this can be done, I will then need to make it work in VBA.

Is it possible?

 

[Misca]

By far the easiest way to do this would be using the Group By -feature of Power Query (=named Get and Transform in the recent versions of Excel). Since the Town and Region columns should depend on Postcode and you want to keep them in your end table add those to the grouping as well.

Also, if you don't have Power Query (or can't download it) you can do the same with a pivot Table. It takes a few mouse clicks more though because you want to turn off the subtotals for each of the column fields.

The beauty of both approaches is you might not even need a macro to repeat the steps if your source data changes. A click of the "Refresh All" -button on your Data tab is all it takes.

There's lots of Group by with Power Query tutorials found on YouTube. Here's one of them: https://youtu.be/A1jZMbPZeAU

 

[Fluff]

Alternatively, if you want VBA, how about

Sub AddUniques()

   Dim Cl As Range
   Dim ValU As String
   Dim itm As Variant
   Dim a As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 3).Value
         If Not .exists(ValU) Then
            .Add ValU, Array(Cl.Resize(, 5).Value, Cl.Offset(, 5).Value)
         Else
            a = .Item(ValU)(1) + Cl.Offset(, 5).Value
            .Item(ValU) = Array(.Item(ValU)(0), a)
         End If
      Next Cl
      For Each itm In .items
         Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("A" & Rows.count).End(xlUp).Offset(1).Resize(, 5).Value = itm(0)
         Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("F" & Rows.count).End(xlUp).Offset(1).Value = itm(1)
      Next itm
   End With
End Sub

This will run on the active sheet & output to "Sheet2"

 

[GAJITCS]

By far the easiest way to do this would be using the Group By -feature of Power Query (=named Get and Transform in the recent versions of Excel). Since the Town and Region columns should depend on Postcode and you want to keep them in your end table add those to the grouping as well.

Also, if you don't have Power Query (or can't download it) you can do the same with a pivot Table. It takes a few mouse clicks more though because you want to turn off the subtotals for each of the column fields.

The beauty of both approaches is you might not even need a macro to repeat the steps if your source data changes. A click of the "Refresh All" -button on your Data tab is all it takes.

There's lots of Group by with Power Query tutorials found on YouTube. Here's one of them: https://youtu.be/A1jZMbPZeAU

Thank you. Will have a look at that.

 

[GAJITCS]

Alternatively, if you want VBA, how about

Sub AddUniques()

    Dim Cl As Range
    Dim ValU As String
    Dim itm As Variant
    Dim a As Long
    
    With CreateObject("scripting.dictionary")
       For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
          ValU = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 3).Value
          If Not .exists(ValU) Then
             .Add ValU, Array(Cl.Resize(, 5).Value, Cl.Offset(, 5).Value)
          Else
             a = .Item(ValU)(1) + Cl.Offset(, 5).Value
             .Item(ValU) = Array(.Item(ValU)(0), a)
          End If
       Next Cl
       For Each itm In .items
          Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("A" & Rows.count).End(xlUp).Offset(1).Resize(, 5).Value = itm(0)
          Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("F" & Rows.count).End(xlUp).Offset(1).Value = itm(1)
       Next itm
    End With
 End Sub

This will run on the active sheet & output to "Sheet2"

Fluff, The more I read, the more I am aware how little I know.

I will try that VBA too, then try harder still to understand it.

Thank you.

 

[GAJITCS]

Fluff,

I understand what it does, but not yet how it does it, nor the coding, but it works.

Why can't the world be more like this place.
In this place, we get to post our questions / problems and other people, kindly and freely give their time and knowledge to help and outright answer the issue.

Can someone please open a forum with the title MrTheWorldWeLiveInAndHowToGetAlongWithEveryone.com

 

[Fluff]

Glad we could help & thanks for the feedback