Dynamically generate section wise report based on another sheet

[jb]

Hello helpers,

I have one excel file containing 2 sheets - Data and Report.
Data sheet has multiple rows having date wise tasks performed by one teacher. Each task is included under some heads (section) like exam, activity, seminar etc.
Teacher will keep on adding data in rows date wise.
There can be new heads coming in future.

Teacher needs to generate dynamic report based on data sheet. The report format is given in report sheet. To have an idea, this report is manually typed.
But required to be generated dynamically.

Please help. Sample file attached.

 

[ETAF]

how about a pivot table
Note your times show 01:30 to 02:30 - so appear by default first as that early in morning/night - should it be PM
that will produce the report and update

what version of excel do you have

 

[mohamed ilyas]

hi

check this.. made from power query, you can enter you data entry in data table format.. second sheet just righ click refresh will be updated..

 

[jb]

how about a pivot table
Note your times show 01:30 to 02:30 - so appear by default first as that early in morning/night - should it be PM
that will produce the report and update

what version of excel do you have

Excel 2010. Also total time of each head is missing

 

[jb]

Not able to use power query. Any suggestion?

Formula based solution or VBA code is preferred for my problem.

 

[ETAF]

i dont provide VBA solutions

 

[Marc L]

Not able to use power query. Formula based solution or VBA code is preferred for my problem

Hello, that must be in the initial post ‼​

​

According to the initial post attachment a beginner level Excel basics VBA event procedure (v2)​

to paste only to the report worksheet module :​

Private Sub Worksheet_Activate()
        Dim Rf As Range
        Application.ScreenUpdating = False
        UsedRange.EntireRow.Delete
        [Data!A1].CurrentRegion.Columns("D:I").Copy [A1]
        UsedRange.Sort [F1], 1, Header:=1
        UsedRange.Subtotal 6, xlSum, 4
   With UsedRange.Columns
       .ClearOutline
       .WrapText = False
       .Borders.Weight = 2
       .Cells(.Rows.Count, 4).Borders(8).LineStyle = xlDouble:  .Cells(.Rows.Count, 4).VerticalAlignment = xlBottom
       .Cells(4) = "Time"
       .Item(4).HorizontalAlignment = xlRight:  .Item(4).NumberFormat = " [h]""h""mm "
       .Rows(1).HorizontalAlignment = xlCenter
       .Item("B:C").NumberFormat = " hh:mm "
        Union(.Item(1), .Item("E:F")).NumberFormat = " @ "
        Set Rf = .Item(6).Find("Total *")
   Do
        Rf(1, -4).Resize(, 3).Clear
        Rf(1, -1).Font.Bold = True
        Rf.Cut Rf(1, 0)
        Set Rf = .Item(6).FindNext(Rf(1, 2))
   Loop Until Rf Is Nothing
        Union(.Cells(.Rows.Count, 5), .Item(6)).Clear
       .AutoFit
   End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[p45cal]

Or a plain pivot:



I've converted the data sheet to a proper table so that when new data is added it will expand automatically.
The pivot will need refreshing if the source data on the data sheet changes; right-click on the pivot and chose Refresh (or this could be done automatically).

Edit after posting: Just saw that @ETAF proposed very similar, however I have included subtotals for the Head

 

[jb]

Or a plain pivot:

View attachment 87485

I've converted the data sheet to a proper table so that when new data is added it will expand automatically.
The pivot will need refreshing if the source data on the data sheet changes; right-click on the pivot and chose Refresh (or this could be done automatically).

Edit after posting: Just saw that @ETAF proposed very similar, however I have included subtotals for the Head

I explored the method of creating pivot table and I tried creating pivot table but I am not getting format like you have created.

First, when I add head column in pivot table it shows the column label as "Row labels".

Next when I add the column date2, from, to and topic, it shows them in the same column of head one after another.

I am not able to understand where I am doing mistake. Can you explain the correct process?

Please look at my file where I have tried to create pivot table.

 

[p45cal]

1. Put the selection somewhere in your new pivot, then on the Design tab, Layout section, Report Layout, choose Show in Tabular Form



2. Your Excel sems to have automatically grouped the To and From fields, so they need to be ungrouped:
Right-click a value in the From field and choose ungroup. Do the same for the To field.



3. There is too much sub-totalling going on so again in the Design tab, choose Do not Show Subtotals:



This removes all subtotalling but we want to keep it for the Head field, so right-click somthing in the Head field and choose Subtotal Head:



Now add Total Time field to the Values area:



Mine defaulted to Count of total time, we need to change that to Sum of Total Time.
See next message (only allowed to add 5 pics per message).

 

[p45cal]

Click on the down arrow (yellow blob below) and choose Value field settings…



then in the dialogue box choose Sum:



but before we click OK on this dialogue box, we'll click on Number Format and change the format to [h]:mm (type it in if it's not in the list):


Then click OK on these dialogues.

After all that you can tart it up as you want; choose a different Pivot table style, remove the +- boxes, change the headers etc.

 

[jb]

Click on the down arrow (yellow blob below) and choose Value field settings…

View attachment 87570

then in the dialogue box choose Sum:

View attachment 87572

but before we click OK on this dialogue box, we'll click on Number Format and change the format to [h]:mm (type it in if it's not in the list):

View attachment 87573
Then click OK on these dialogues.

After all that you can tart it up as you want; choose a different Pivot table style, remove the +- boxes, change the headers etc.

Thank you for your help. I followed your steps. Everything works fine but when I am adding from and to column, it is not preserving the format in which I have entered the data. It attach AM or PM with time. Kindly suggest how to preserve time format as per original data in pivot table.

Also it adds one column with title Hours2 between From and To column. Can you please explain this behaviour? Also suggest how to avoid this extra column. Thank you in advance.

 

[p45cal]

Also it adds one column with title Hours2 between From and To column. Can you please explain this behaviour?

It's the grouping it does for you automatically - a pain. You'll need to ungroup again, as mentioned in step 2. There may be a setting somewhere to prevent it being 'helpful'!

It attach AM or PM with time. Kindly suggest how to preserve time format as per original data in pivot table.

You'll need to go into Number format in Field settings to reset the formatting.

 

[Marc L]

It attach AM or PM with time.

No such issue with post #7 …​

​

 

[jb]

It's the grouping it does for you automatically - a pain. You'll need to ungroup again, as mentioned in step 2. There may be a setting somewhere to prevent it being 'helpful'!

You'll need to go into Number format in Field settings to reset the formatting.

Sir, I copied pivot table suggested by p45cal and applied on my actual data. Last column is sum of total time column. It gives wired answer for some rows. Say for example, 17-06-2024 - 9:40 to 10:30 it gives 00:50 which is correct.
But for 24-06-2024, 27-06-2024, 28-06-2024 and 01-07-2024 - 9:40 to 10:30 it gives 01:40 which is incorrect.
This happens for 25-06-2024 - 11:30 to 12:20 - it should give 00:50 but giving 01:40.
How this happened?

 

[Marc L]

According to your guessing challenge check the source data are numeric rather than text unlike your 'dates' in your initial post attachment …​

​

 

[jb]

According to your guessing challenge check the source data are numeric rather than text unlike your 'dates' in your initial post attachment …​

​

Sir, total time column is based on subtraction of To minus From column. And I checked the format. It is not text. It is in hh:mm format. In the data sheet, it calculates the time difference correctly. But in pivot table for few entries correct answer showing and for few entries wrong answer.

I am attaching my original file here.

 

[p45cal]

It's the sum of all lines in your source data that match; for example, for
25-06-2024 the pivot shows:


because 2 lines from your data fulfil the Head (Session), Date_R (25-06-2024), From (9:40), To (10:30), Topic (both exactly the same), so both rows' time of 50mins are summed (=1:40).



If you see unexpected results like this, you can double-click on the result (1:40 in the Sum of Total Time column), and Excel will create a new sheet showing the rows of data in the source that have been used to get the result you double-clicked on. It looks like this:



You can delete the sheet after viewing it.

If you want to see these as separate rows in the pivot, include another field in the pivot that has different values, such as the Class field or the Subject Code.

 

[jb]

It's the sum of all lines in your source data that match; for example, for
25-06-2024 the pivot shows:
View attachment 87597

because 2 lines from your data fulfil the Head (Session), Date_R (25-06-2024), From (9:40), To (10:30), Topic (both exactly the same), so both rows' time of 50mins are summed (=1:40).

View attachment 87596

If you see unexpected results like this, you can double-click on the result (1:40 in the Sum of Total Time column), and Excel will create a new sheet showing the rows of data in the source that have been used to get the result you double-clicked on. It looks like this:

View attachment 87598

You can delete the sheet after viewing it.

If you want to see these as separate rows in the pivot, include another field in the pivot that has different values, such as the Class field or the Subject Code.

It worked. Thank you so much sir for your help and explanation. Thank you again.