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

G

GAJITCS

Board Regular
Joined
Apr 21, 2015
Messages
66
I have a table of data as below:

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

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Company
[/TD]
[TD]Add1
[/TD]
[TD]Town
[/TD]
[TD]Postcode
[/TD]
[TD]Region
[/TD]
[TD]Pieces
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC
[/TD]
[TD]1 Alpha Street
[/TD]
[TD]Hull
[/TD]
[TD]HU1 2PP
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]DEF
[/TD]
[TD]23 Delta Road
[/TD]
[TD]Leeds
[/TD]
[TD]LE32 2UH
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ABC
[/TD]
[TD]3 Bravo Close
[/TD]
[TD]Hull
[/TD]
[TD]HU1 4HY
[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]DEF
[/TD]
[TD]4 Echo Lane
[/TD]
[TD]Derby
[/TD]
[TD]DE3 8GF
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]GHI
[/TD]
[TD]8 Foxtrot Avenue
[/TD]
[TD]Halifax
[/TD]
[TD]HA1 2RR
[/TD]
[TD]4
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ABC[/TD]
[TD]Bravo Close[/TD]
[TD]Corby[/TD]
[TD]CO17 6WS[/TD]
[TD]5[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]GHI[/TD]
[TD]8 Foxtrot Avenue[/TD]
[TD]Hessle[/TD]
[TD]HU3 3WQ[/TD]
[TD]1[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

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

Is it possible?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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
 
Upvote 0
Alternatively, if you want VBA, how about
Code: 
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"
 
Upvote 0
Misca said:
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
Click to expand...


Thank you. Will have a look at that.
 
Last edited:
Upvote 0
Fluff said:
Alternatively, if you want VBA, how about
Code: 
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"
Click to expand...


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.
 
Last edited:
Upvote 0
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
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top