Table summary formula

[Tigerexcel]

Hi all,

I have a number of discrete tables which need summarising. they are in the format shown below, there are no formulae in them.
I need a formula that will pick up the diploma name and return some of the totals in a compact list, something like:
Diploma Pass Rate Transition
Art 91% 261
Maths 91% 261
etc.

	Diploma of Art
Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
Art	2018-2	29	4	69%	95%	65.0	19
Art	2018-1	76	5	74%	92%	62.9	55
Art	2017-3	10	1	60%	92%	63.8	6
Art	2017-2	36	2	64%	91%	61.4	22
Art	2017-1	111		83%	93%	63.6	90
	TOTAL	495	120	55%	91%	62.5	261
	Diploma of Maths
Diploma	Start Trimester	Commence	Enrolled	Start / Compl Rate	Pass Rate	Avg Mark	Transition
Maths	2018-2	29	4	69%	95%	65.0	19
Maths	2018-1	76	5	74%	92%	62.9	55
Maths	2017-3	10	1	60%	92%	63.8	6
Maths	2017-2	36	2	64%	91%	61.4	22
Maths	2017-1	111		83%	93%	63.6	90
	TOTAL	495	120	55%	91%	62.5	261

 

[GraH]

You have Office 2016, so PQ is available under the Data Ribbon -> Get & Transform. Try it before you "clean" your data. It's kind of the reason PQ exists .
I don't really get your question about match having to skim the same column twice? As in finding a first match, then a second, third, x-th one?

 

[Tigerexcel]

Hi,
In this case I would use a Power Query solution. Assuming all those tables are on the same sheet called "Diplomas".

1. get data from file -> from workbook
2. navigate to the workbook (even if it is the one that's open )
3. in the navigator select the sheet "Diplomas" and hit Transform Data button
4. You may need to demote headers when the first table starts in row 2, PQ may pick this up as a column header.
5. Add a custom column, like in example below picture 1
6. With this new column highlighted, on the transform step select Fill and fill down
7. Like in Excel filter the table on Column 2 and Remove "null" from the selection
8. Promote headers (use first row as headers is a button on the Transform tab)
9. Filter out again, on any of the columns the repeated headers
10. Finally select the rows containing 2019 (which we can make a dynamic selection if required)
11. Move last column to first position (select header, and mouse right click, look for "Move" in the context menu and move to beginning)
12. Save and load to Excel as table or as pivot table.

View attachment 11589

Result looks like this
View attachment 11590

My current computer has only Excel 2013 but I did source a computer with Excel 2016 and got the Power Query, how did you get the Add Conditional Column menu to come up?

 

[GraH]

It's over here

 

[Tigerexcel]

Hi Gra,

Going back to step 6 in Post 9, I get the word Column1 returned rather than the actual cell contents which would be Table 3.1 etc. Is there another setting I need to change?

 

[GraH]

Hi again,

Hard to tell without actually seeing what you are doing, but I suspect you are fooled by the screenshot. Do not type "Column1" in the helper screen for the conditional column. In the Output section use the drop down "Column" and not value then select the correct column. Let me know if that would work.

 

[Tigerexcel]

Hi again,

Hard to tell without actually seeing what you are doing, but I suspect you are fooled by the screenshot. Do not type "Column1" in the helper screen for the conditional column. In the Output section use the drop down "Column" and not value then select the correct column. Let me know if that would work.

Thank you and I am easily fooled, that did it. One thing I can't work out is do you know how to apply the query once created to another worksheet in the same file?
Also wonder why you can't just type Column1 above?

 

[GraH]

Hi,

1. You probably need to change tactics a bit I guess. So you have multiple tables on different sheets in the same workbook, is that the correct situation?
If so, then start by Data -> From File -> From Workbook. Point to the Excel file in the explorer . I know it's the one that's open,, bear with.

In the navigator you will see a screen that shows a "folder" which is your "file" and in that folder all your sheets. Click on the "folder" and on "transform".
This opens a PQ window containing the sheetsnames, some more columns, and an object "Table". Keep this column and Expand it.*

* Be careful to filter out your "consolidation" in one of these steps before expanding. Otherwise you have a never ending loop. Then continue as you've already done.

2. Well, do recall you are actually writing a script in M-language through these applied steps. So there is a different between typing in a value "Column1" and referring to a table [Column1]. Syntax does matter.

Possible alternative for 1 (if it seems to complicated) is making a copy of the query in the query pane (right click and select duplicated from the menu). Then open this new query and change the source step.

 

[Tigerexcel]

Hi,

1. You probably need to change tactics a bit I guess. So you have multiple tables on different sheets in the same workbook, is that the correct situation?
If so, then start by Data -> From File -> From Workbook. Point to the Excel file in the explorer . I know it's the one that's open,, bear with.

In the navigator you will see a screen that shows a "folder" which is your "file" and in that folder all your sheets. Click on the "folder" and on "transform".
This opens a PQ window containing the sheetsnames, some more columns, and an object "Table". Keep this column and Expand it.*

* Be careful to filter out your "consolidation" in one of these steps before expanding. Otherwise you have a never ending loop. Then continue as you've already done.

2. Well, do recall you are actually writing a script in M-language through these applied steps. So there is a different between typing in a value "Column1" and referring to a table [Column1]. Syntax does matter.

Possible alternative for 1 (if it seems to complicated) is making a copy of the query in the query pane (right click and select duplicated from the menu). Then open this new query and change the source step.

Hi again G.,

1. It was more you have set up the query and it works effectively. I wanted to try and apply the query to another worksheet also in the same file. I should say that I'm currently using Excel 2013, when I do a Google search Excel 2016 is quite straightforward. I'll get it soon but it's not apparent via the 2103 menu.
2. I did try to alter the M script and did try a few combinations including square brackets and inverted commas but it only worked after I selected it as per your previous post.

I see a lot of potential with Power Query but it's not the most intuitive bit of software. Thanks G. for getting back to me so promptly and enjoy the upcoming warmer weather, here it's been cold lately as we approach Winter.

 

[GraH]

Whereas I think it is the most intuitive thing in Excel ever It will certainly grow (fast) on you.
We have had one of the 4 warmest and driest months of April ever. We are pleased it is raining for a change. Go figure. Thanks for all the likes and see you around on the forum. Enjoy (?) Winter, stay warm.