consolidate and sum the duplicate rows in a table

Rmishra04

New Member
Joined
Sep 20, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Can anyone please help me to figure out how can I consolidate the rows and add the occurrence value mentioned in each row.
To have a lucid sense of my data set, please note that the whole data table contains 40k rows and 5 cols. A segment of it is shared below:

Untitled Spreadsheet
Reported OnCivic MasterSub CivicWardOccurence
01 Dec 2018BuildingsHeavy Leakage From CeilingF/N4
01 Dec 2018BuildingsHeavy Leakage From CeilingL1
01 Dec 2018BuildingsHeavy Leakage From CeilingK/E1
01 Dec 2018BuildingsHeavy Leakage From CeilingG/N1
01 Dec 2018BuildingsHeavy Leakage From CeilingK/W5
01 Dec 2018BuildingsHeavy Leakage From CeilingD1
01 Dec 2018BuildingsHeavy Leakage From CeilingN1
01 Dec 2018BuildingsHeavy Leakage From CeilingP/S1
01 Dec 2018BuildingsHeavy Leakage From CeilingM/E1
01 Dec 2018BuildingsHeavy Leakage From CeilingA1
01 Dec 2018BuildingsHeavy Leakage From CeilingF/N3
01 Dec 2018BuildingsHeavy Leakage From CeilingC2
01 Dec 2018BuildingsHeavy Leakage From CeilingP/N1
01 Dec 2018BuildingsHeavy Leakage From CeilingD1
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesH/E2
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesD5
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/E6
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesS4
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesD2
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/W3
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesP/N5
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesL7
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesB5
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesE4
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesM/W3
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesR/S3
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesA1
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/S3
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesC2
01 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/N2

So in a continuous fashion, I have various reporting dates of different civic and sub civic matters in different wards of the city along with the occurrence of the complaints. I have bolded a few rows and what exactly I am trying to do is combining these rows to add the occurrence value and form a singlr row in the respective sub-civic categories.

Every time I am using the consolidate tool, resulting in a popup msg "source reference overlaps destination area."
Can anyone help?!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
143
Office Version
  1. 2019
Platform
  1. Windows
Rmishra04 Yes someone can help you. We just have to clarify what it is you want. So far I can only see that you Bolded Ward D. Is that what you want? Also what are you using to get the popup. VBA or some functions or formulas. Notice I used this XLB22. If you could use the same it will help out. Well there going to be lots of questions and answers. Let the discussion begin

XL2BB - Excel Range to BBCode




20-09-20 civic.xlsx
ABCDE
1Reported OnCivic MasterSub CivicWardOccurence
201 Dec 2018BuildingsHeavy Leakage From CeilingF/N4
301 Dec 2018BuildingsHeavy Leakage From CeilingL1
401 Dec 2018BuildingsHeavy Leakage From CeilingK/E1
501 Dec 2018BuildingsHeavy Leakage From CeilingG/N1
601 Dec 2018BuildingsHeavy Leakage From CeilingK/W5
701 Dec 2018BuildingsHeavy Leakage From CeilingD1
801 Dec 2018BuildingsHeavy Leakage From CeilingN1
901 Dec 2018BuildingsHeavy Leakage From CeilingP/S1
1001 Dec 2018BuildingsHeavy Leakage From CeilingM/E1
1101 Dec 2018BuildingsHeavy Leakage From CeilingA1
1201 Dec 2018BuildingsHeavy Leakage From CeilingF/N3
1301 Dec 2018BuildingsHeavy Leakage From CeilingC2
1401 Dec 2018BuildingsHeavy Leakage From CeilingP/N1
1501 Dec 2018BuildingsHeavy Leakage From CeilingD1
1601 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesH/E2
1701 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesD5
1801 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/E6
1901 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesS4
2001 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesD2
2101 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/W3
2201 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesP/N5
2301 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesL7
2401 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesB5
2501 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesE4
2601 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesM/W3
2701 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesR/S3
2801 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesA1
2901 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/S3
3001 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesC2
3101 Dec 2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/N2
Civic Data
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
try Power Query / Get&Transform
Reported OnCivic MasterSub CivicWardOccurenceReported OnCivic MasterSub CivicWardCccurence
01-Dec-18BuildingsHeavy Leakage From CeilingF/N401/12/2018BuildingsHeavy Leakage From CeilingF/N7
01-Dec-18BuildingsHeavy Leakage From CeilingL101/12/2018BuildingsHeavy Leakage From CeilingL1
01-Dec-18BuildingsHeavy Leakage From CeilingK/E101/12/2018BuildingsHeavy Leakage From CeilingK/E1
01-Dec-18BuildingsHeavy Leakage From CeilingG/N101/12/2018BuildingsHeavy Leakage From CeilingG/N1
01-Dec-18BuildingsHeavy Leakage From CeilingK/W501/12/2018BuildingsHeavy Leakage From CeilingK/W5
01-Dec-18BuildingsHeavy Leakage From CeilingD101/12/2018BuildingsHeavy Leakage From CeilingD2
01-Dec-18BuildingsHeavy Leakage From CeilingN101/12/2018BuildingsHeavy Leakage From CeilingN1
01-Dec-18BuildingsHeavy Leakage From CeilingP/S101/12/2018BuildingsHeavy Leakage From CeilingP/S1
01-Dec-18BuildingsHeavy Leakage From CeilingM/E101/12/2018BuildingsHeavy Leakage From CeilingM/E1
01-Dec-18BuildingsHeavy Leakage From CeilingA101/12/2018BuildingsHeavy Leakage From CeilingA1
01-Dec-18BuildingsHeavy Leakage From CeilingF/N301/12/2018BuildingsHeavy Leakage From CeilingC2
01-Dec-18BuildingsHeavy Leakage From CeilingC201/12/2018BuildingsHeavy Leakage From CeilingP/N1
01-Dec-18BuildingsHeavy Leakage From CeilingP/N101/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesH/E2
01-Dec-18BuildingsHeavy Leakage From CeilingD101/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesD7
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesH/E201/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/E6
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesD501/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesS4
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesK/E601/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesK/W3
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesS401/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesP/N5
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesD201/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesL7
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesK/W301/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesB5
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesP/N501/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesE4
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesL701/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesM/W3
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesB501/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesR/S3
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesE401/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesA1
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesM/W301/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/S3
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesR/S301/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesC2
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesA101/12/2018BuildingsPrivate Land/ Building/ Society/ FactoriesG/N2
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesG/S3
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesC2
01-Dec-18BuildingsPrivate Land/ Building/ Society/ FactoriesG/N2

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Reported On", type date}, {"Civic Master", type text}, {"Sub Civic", type text}, {"Ward", type text}, {"Occurence", Int64.Type}}),
    Group = Table.Group(Type, {"Reported On", "Civic Master", "Sub Civic", "Ward"}, {{"Cccurence", each List.Sum([Occurence]), type number}})
in
    Group
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or just use a Pivot Table
Reported OnCivic MasterSub CivicWardSum of Occurence
01-Dec-18BuildingsHeavy Leakage From CeilingA1
C2
D2
L1
N1
F/N7
G/N1
K/E1
K/W5
M/E1
P/N1
P/S1
Private Land/ Building/ Society/ FactoriesA1
B5
C2
D7
E4
L7
S4
G/N2
G/S3
H/E2
K/E6
K/W3
M/W3
P/N5
R/S3
 

Rmishra04

New Member
Joined
Sep 20, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks man! It helped a lot.
One more confirmation, if you allow :3
It can't be done using consolidate tool. Right?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I even didn't try that if I have better and faster tools
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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
Top