consolidate and sum the duplicate rows in a table

[Rmishra04]

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 On	Civic Master	Sub Civic	Ward	Occurence
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	F/N	4
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	L	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	K/E	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	G/N	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	K/W	5
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	D	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	N	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	P/S	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	M/E	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	A	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	F/N	3
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	C	2
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	P/N	1
01 Dec 2018	Buildings	Heavy Leakage From Ceiling	D	1
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	H/E	2
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	D	5
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	K/E	6
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	S	4
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	D	2
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	K/W	3
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	P/N	5
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	L	7
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	B	5
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	E	4
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	M/W	3
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	R/S	3
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	A	1
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	G/S	3
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	C	2
01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	G/N	2

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

 

[Ezguy4u]

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
	A	B	C	D	E
1	Reported On	Civic Master	Sub Civic	Ward	Occurence
2	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	F/N	4
3	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	L	1
4	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	K/E	1
5	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	G/N	1
6	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	K/W	5
7	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	D	1
8	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	N	1
9	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	P/S	1
10	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	M/E	1
11	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	A	1
12	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	F/N	3
13	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	C	2
14	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	P/N	1
15	01 Dec 2018	Buildings	Heavy Leakage From Ceiling	D	1
16	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	H/E	2
17	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	D	5
18	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	K/E	6
19	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	S	4
20	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	D	2
21	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	K/W	3
22	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	P/N	5
23	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	L	7
24	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	B	5
25	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	E	4
26	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	M/W	3
27	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	R/S	3
28	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	A	1
29	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	G/S	3
30	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	C	2
31	01 Dec 2018	Buildings	Private Land/ Building/ Society/ Factories	G/N	2
Civic Data

 

[sandy666]

try Power Query / Get&Transform

Reported On	Civic Master	Sub Civic	Ward	Occurence		Reported On	Civic Master	Sub Civic	Ward	Cccurence
01-Dec-18	Buildings	Heavy Leakage From Ceiling	F/N	4		01/12/2018	Buildings	Heavy Leakage From Ceiling	F/N	7
01-Dec-18	Buildings	Heavy Leakage From Ceiling	L	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	L	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	K/E	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	K/E	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	G/N	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	G/N	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	K/W	5		01/12/2018	Buildings	Heavy Leakage From Ceiling	K/W	5
01-Dec-18	Buildings	Heavy Leakage From Ceiling	D	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	D	2
01-Dec-18	Buildings	Heavy Leakage From Ceiling	N	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	N	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	P/S	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	P/S	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	M/E	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	M/E	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	A	1		01/12/2018	Buildings	Heavy Leakage From Ceiling	A	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	F/N	3		01/12/2018	Buildings	Heavy Leakage From Ceiling	C	2
01-Dec-18	Buildings	Heavy Leakage From Ceiling	C	2		01/12/2018	Buildings	Heavy Leakage From Ceiling	P/N	1
01-Dec-18	Buildings	Heavy Leakage From Ceiling	P/N	1		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	H/E	2
01-Dec-18	Buildings	Heavy Leakage From Ceiling	D	1		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	D	7
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	H/E	2		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	K/E	6
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	D	5		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	S	4
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	K/E	6		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	K/W	3
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	S	4		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	P/N	5
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	D	2		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	L	7
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	K/W	3		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	B	5
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	P/N	5		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	E	4
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	L	7		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	M/W	3
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	B	5		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	R/S	3
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	E	4		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	A	1
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	M/W	3		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	G/S	3
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	R/S	3		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	C	2
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	A	1		01/12/2018	Buildings	Private Land/ Building/ Society/ Factories	G/N	2
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	G/S	3
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	C	2
01-Dec-18	Buildings	Private Land/ Building/ Society/ Factories	G/N	2

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]

or just use a Pivot Table

Reported On	Civic Master	Sub Civic	Ward	Sum of Occurence
01-Dec-18	Buildings	Heavy Leakage From Ceiling	A	1
			C	2
			D	2
			L	1
			N	1
			F/N	7
			G/N	1
			K/E	1
			K/W	5
			M/E	1
			P/N	1
			P/S	1
		Private Land/ Building/ Society/ Factories	A	1
			B	5
			C	2
			D	7
			E	4
			L	7
			S	4
			G/N	2
			G/S	3
			H/E	2
			K/E	6
			K/W	3
			M/W	3
			P/N	5
			R/S	3

 

[Rmishra04]

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

 

[sandy666]

I even didn't try that if I have better and faster tools

 

[Rmishra04]

Yeah ofcourse. Thanks!

 

[sandy666]

You are welcome
Have a nice day