Extract Data from Multi-dimensional Structure using Excel Formulas

[mrahulkanth]

I have 2 Tables of which Table1 contains the full data pulled from the required sources and the following is the format of that how it looks:

For Table1, all the details of the Volumes and Errors of the respective Sub-functions & Team Members will be pulled automatically through a macro.

Note: Table1 is in the Table Range and Table2 is the Normal Range.

Now, I would like to pull the summarized information in one sheet from Table1 as shown in the below in Table2 format:

I have tried multiple ways of implementing formulas, but I'm unable to get the Sub-functions, Sum of the Volumes & Errors from the Sub-Functions of the Selected Team Member & the Date from the dropdowns.

Appreciate your help!

Please note that I have posted this question in the other forum: Extract Data from Multi dimensional Structure using Excel Formulas

Just trying to get the solution as quickly as possible as it is little important.

 

[severynm]

Hi.

I'd echo what the commenter on SO said also. The most robust solution is to load it into Power Query to to the transformations (column unpivoting, etc.), write measures to calculate the percentages, and add them all into a pivot table.

However, you could also piece it together with a fair bit of manual labor and some SUMIFS.

MrExcel.xlsm
	A	B	C	D	E	F	G
10	Name:	TM 1
11	Date:	mmm-yyyy
12				Volumes	Errors	Accuracy %	Error %
13			Sub Function 1	100	20	80%	20%
14			Sub Function 2	100	20	80%	20%
15			…
Sheet6

Cell Formulas
Range	Formula
D13	D13	=SUMIFS(Table4[subfunction1_volumes],Table4[team members],$B$10,Table4[date],$B$11)
E13	E13	=SUMIFS(Table4[subfunction1_errors],Table4[team members],$B$10,Table4[date],$B$11)
F13:F14	F13	=1-E13/D13
G13:G14	G13	=E13/D13
D14	D14	=SUMIFS(Table4[subfunction2_volumes],Table4[team members],$B$10,Table4[date],$B$11)
E14	E14	=SUMIFS(Table4[subfunction2_errors],Table4[team members],$B$10,Table4[date],$B$11)

 

[mrahulkanth]

Hi.

I'd echo what the commenter on SO said also. The most robust solution is to load it into Power Query to to the transformations (column unpivoting, etc.), write measures to calculate the percentages, and add them all into a pivot table.

However, you could also piece it together with a fair bit of manual labor and some SUMIFS.

MrExcel.xlsm
	A	B	C	D	E	F	G
10	Name:	TM 1
11	Date:	mmm-yyyy
12				Volumes	Errors	Accuracy %	Error %
13			Sub Function 1	100	20	80%	20%
14			Sub Function 2	100	20	80%	20%
15			…
Sheet6

Cell Formulas
Range	Formula
D13	D13	=SUMIFS(Table4[subfunction1_volumes],Table4[team members],$B$10,Table4[date],$B$11)
E13	E13	=SUMIFS(Table4[subfunction1_errors],Table4[team members],$B$10,Table4[date],$B$11)
F13:F14	F13	=1-E13/D13
G13:G14	G13	=E13/D13
D14	D14	=SUMIFS(Table4[subfunction2_volumes],Table4[team members],$B$10,Table4[date],$B$11)
E14	E14	=SUMIFS(Table4[subfunction2_errors],Table4[team members],$B$10,Table4[date],$B$11)

Thank you so much for the suggestion & formula help. ?

I have created a VBA code for the entire procedure. Now it is working fine.

Once again, thank you.