List down values based on main Tag and a sub tag and get totals

[nisanthp]

Hi .

I've a data set in which there are data and Sub data and Sub data has a quanity against each line. The data is not in particular order and data will get added to the bottom of the list. I want to make a dash board like this :

Data	Sub data	Total of sub data
A	A1	15
	A2	1
	A3	33
	A4	55
B	B1	41
	B2	33
	B3	92
C	C1	90
	C2	50

The Data/Sub data need not be dynamic, can close for a particulat set of day. Pivot table can do this but the issues is there are lot of other data columns in between the Sub data & qty which is not required for this dash board.

Appreciate any help on this.

 

[alansidman]

What does the source data look like that generated the table you are showing in the original post. We are not mind readers here and need examples to provide workable solutions. Please XL2BB functionality.

 

[nisanthp]

Sorry my bad, orginal data set will be like this :

C	C1	148	m²
C	C7	26	m²
A	A1	470	m²
C	C9	9	m²
A	A12	65	m²
A	A2	51	m²
G	ST-23	15	m²
A	A1	191	m²
F	F2	84	m²
C	C4	27	m²
G	G7	37	m²
D	D2	245	m²
G	G5	60	m²
C	C2	223	m²
A	A14	202	m²
F	F1	114	m²
A	A13	108	m²
A	A8	105	m²
A	A11	108	m²
A	A8	116	m²
G	G5	165	m²
C	C3	68	m²
A	A7	843	m²
G	G9	27	m²
C	C6	35	m²
C	C5	96	m²
A	A8	8	m²
A	A13	324	m²
G	G3	8	m²
A	A12	142	m²
G	ST-17B	3	m²
A	A7	137	m²
F	F1	19	m²
A	A11	197	m²
A	A2	156	m²

 

[alansidman]

One method is with Power Query shown in F:H columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column2"}, {{"Sum", each List.Sum([Column3]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Column1", Order.Ascending}, {"Column2", Order.Ascending}})
in
    #"Sorted Rows"

Another method is with GroupBy Function

=GROUPBY(Table1[[#All],[Column1]:[Column2]],Table1[[#All],[Column3]],SUM,3,0)

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	Column1	Column2	Column3	Column4		Column1	Column2	Sum		Column1	Column2	Column3
2	C	C1	148	m²		A	A1	661		A	A1	661
3	C	C7	26	m²		A	A11	305		A	A11	305
4	A	A1	470	m²		A	A12	207		A	A12	207
5	C	C9	9	m²		A	A13	432		A	A13	432
6	A	A12	65	m²		A	A14	202		A	A14	202
7	A	A2	51	m²		A	A2	207		A	A2	207
8	G	ST-23	15	m²		A	A7	980		A	A7	980
9	A	A1	191	m²		A	A8	229		A	A8	229
10	F	F2	84	m²		C	C1	148		C	C1	148
11	C	C4	27	m²		C	C2	223		C	C2	223
12	G	G7	37	m²		C	C3	68		C	C3	68
13	D	D2	245	m²		C	C4	27		C	C4	27
14	G	G5	60	m²		C	C5	96		C	C5	96
15	C	C2	223	m²		C	C6	35		C	C6	35
16	A	A14	202	m²		C	C7	26		C	C7	26
17	F	F1	114	m²		C	C9	9		C	C9	9
18	A	A13	108	m²		D	D2	245		D	D2	245
19	A	A8	105	m²		F	F1	133		F	F1	133
20	A	A11	108	m²		F	F2	84		F	F2	84
21	A	A8	116	m²		G	G3	8		G	G3	8
22	G	G5	165	m²		G	G5	225		G	G5	225
23	C	C3	68	m²		G	G7	37		G	G7	37
24	A	A7	843	m²		G	G9	27		G	G9	27
25	G	G9	27	m²		G	ST-17B	3		G	ST-17B	3
26	C	C6	35	m²		G	ST-23	15		G	ST-23	15
27	C	C5	96	m²
28	A	A8	8	m²
29	A	A13	324	m²
30	G	G3	8	m²
31	A	A12	142	m²
32	G	ST-17B	3	m²
33	A	A7	137	m²
34	F	F1	19	m²
35	A	A11	197	m²
36	A	A2	156	m²
Sheet1

Cell Formulas
Range	Formula
J1:L26	J1	=GROUPBY(Table1[[#All],[Column1]:[Column2]],Table1[[#All],[Column3]],SUM,3,0)
Dynamic array formulas.

 

[nisanthp]

Thanks for the reply. I dont have Groupby option in my excel version and new to Power Query as well. Will try to learn PQ and see

 

[alansidman]

What version of Excel are you using? Please update your profile to show the particular version so that we can cater our response to the specific Excel version you are using. Here is some PQ information that should help you.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions