Generating an an excel report from data sheet

P

Prasanna arachchi

New Member
Joined
Jan 9, 2025
Messages
4
Office Version
  1. 365
Hi Team
I have following table of our employees those who have booked Annual leave i need to enter a slicer or report to an extended work sheet that automatically pick the name of employee and the starting date of leave , fining date of leave and the department even it is a macros, it is ok
Thanks




Start TimeFinish TimeEmployeeApproved ByTotal HoursDate of LeaveLeave TypeRequestIdDepartment
Alam Moreira MoralesAlam Moreira Morales174.8
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 06 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 07 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 10 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 13 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 14 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 17 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 20 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 21 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 24 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 28 Jan 2025ANNUAL LVESteel Shop - Fabrication
Amrik Singh ChohanAmrik Singh Chohan76
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Mon, 06 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Tue, 07 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Wed, 08 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Thu, 09 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Fri, 10 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Mon, 13 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Tue, 14 Jan 2025ANNUAL LVESteel Shop - Fabrication
Bi NgachhiBi Ngachhi7.6
6:00 AM2:00 PMBi Ngachhi<manually booked>7.6Wed, 08 Jan 2025ANNUAL LVESteel Shop - Fabrication
Billy PitmanBilly Pitman68.4
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Mon, 07 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Tue, 08 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Wed, 09 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Thu, 10 Apr 2025ANNUAL LVECONCRETE - Tullamarine
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
With Power Query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Approved By] = "<manually booked>")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date of Leave", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Leave Starts", each List.Min([Date of Leave]), type nullable date}, {"Leave Ends", each List.Max([Date of Leave]), type nullable date}, {"Department", each _, type table [Start Time=number, Finish Time=number, Employee=text, Approved By=text, Total Hours=number, Date of Leave=nullable date, Leave Type=text, RequestId=text, Department=any]}}),
    #"Expanded Department" = Table.ExpandTableColumn(#"Grouped Rows", "Department", {"RequestId"}, {"RequestId"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Department", {"Employee", "Leave Starts", "Leave Ends"})
in
    #"Removed Duplicates"

Book2
KLMN
1EmployeeLeave StartsLeave EndsRequestId
2Alam Moreira Morales1/6/20251/28/2025Steel Shop - Fabrication
3Amrik Singh Chohan1/6/20251/14/2025Steel Shop - Fabrication
4Bi Ngachhi1/8/20251/8/2025Steel Shop - Fabrication
5Billy Pitman4/7/20254/10/2025CONCRETE - Tullamarine
Sheet1
 
Upvote 0
Prasanna arachchi said:
Hi Team
I have following table of our employees those who have booked Annual leave i need to enter a slicer or report to an extended work sheet that automatically pick the name of employee and the starting date of leave , fining date of leave and the department even it is a macros, it is ok
Thanks




Start TimeFinish TimeEmployeeApproved ByTotal HoursDate of LeaveLeave TypeRequestIdDepartment
Alam Moreira MoralesAlam Moreira Morales174.8
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 06 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 07 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 10 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 13 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 14 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 17 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Mon, 20 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 21 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Fri, 24 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAlam Moreira Morales<manually booked>7.6Tue, 28 Jan 2025ANNUAL LVESteel Shop - Fabrication
Amrik Singh ChohanAmrik Singh Chohan76
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Mon, 06 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Tue, 07 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Wed, 08 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Thu, 09 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Fri, 10 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Mon, 13 Jan 2025ANNUAL LVESteel Shop - Fabrication
6:00 AM2:00 PMAmrik Singh Chohan<manually booked>7.6Tue, 14 Jan 2025ANNUAL LVESteel Shop - Fabrication
Bi NgachhiBi Ngachhi7.6
6:00 AM2:00 PMBi Ngachhi<manually booked>7.6Wed, 08 Jan 2025ANNUAL LVESteel Shop - Fabrication
Billy PitmanBilly Pitman68.4
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Mon, 07 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Tue, 08 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Wed, 09 Apr 2025ANNUAL LVECONCRETE - Tullamarine
6:00 AM2:00 PMBilly Pitman<manually booked>7.6Thu, 10 Apr 2025ANNUAL LVECONCRETE - Tullamarine
Click to expand...
Am I correct in assuming that the report needs to consider and accommodate multiple leave periods for each employee?
 
Upvote 0
alansidman said:
With Power Query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Approved By] = "<manually booked>")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date of Leave", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Leave Starts", each List.Min([Date of Leave]), type nullable date}, {"Leave Ends", each List.Max([Date of Leave]), type nullable date}, {"Department", each _, type table [Start Time=number, Finish Time=number, Employee=text, Approved By=text, Total Hours=number, Date of Leave=nullable date, Leave Type=text, RequestId=text, Department=any]}}),
    #"Expanded Department" = Table.ExpandTableColumn(#"Grouped Rows", "Department", {"RequestId"}, {"RequestId"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Department", {"Employee", "Leave Starts", "Leave Ends"})
in
    #"Removed Duplicates"

Book2
KLMN
1EmployeeLeave StartsLeave EndsRequestId
2Alam Moreira Morales1/6/20251/28/2025Steel Shop - Fabrication
3Amrik Singh Chohan1/6/20251/14/2025Steel Shop - Fabrication
4Bi Ngachhi1/8/20251/8/2025Steel Shop - Fabrication
5Billy Pitman4/7/20254/10/2025CONCRETE - Tullamarine
Sheet1
Click to expand...

you are a legend !!
Than you very much Alan
 
Upvote 0
alansidman said:
With Power Query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Approved By] = "<manually booked>")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date of Leave", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Leave Starts", each List.Min([Date of Leave]), type nullable date}, {"Leave Ends", each List.Max([Date of Leave]), type nullable date}, {"Department", each _, type table [Start Time=number, Finish Time=number, Employee=text, Approved By=text, Total Hours=number, Date of Leave=nullable date, Leave Type=text, RequestId=text, Department=any]}}),
    #"Expanded Department" = Table.ExpandTableColumn(#"Grouped Rows", "Department", {"RequestId"}, {"RequestId"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Department", {"Employee", "Leave Starts", "Leave Ends"})
in
    #"Removed Duplicates"

Book2
KLMN
1EmployeeLeave StartsLeave EndsRequestId
2Alam Moreira Morales1/6/20251/28/2025Steel Shop - Fabrication
3Amrik Singh Chohan1/6/20251/14/2025Steel Shop - Fabrication
4Bi Ngachhi1/8/20251/8/2025Steel Shop - Fabrication
5Billy Pitman4/7/20254/10/2025CONCRETE - Tullamarine
Sheet1
Click to expand...
Hi Alan ,can i download the file ? THTA YOU HAVE CREATED
 
Upvote 0
Since you didn't show us your desired result, this is how I read your question.
Code: 
Sub test()
    Dim a, i&, ii&, n&
    a = Sheets("sheet1").[a1].CurrentRegion.Value
    For i = 3 To UBound(a, 1)
        If a(i, 5) <> "" Then
            n = n + 1
            a(n, 1) = a(i, 3): a(n, 2) = a(i, 6)
            a(n, 3) = a(i, 6): a(n, 4) = a(i, 8)
            ii = 1
            If i + ii < UBound(a, 1) Then
                Do While a(i + ii, 6) = DateAdd("d", ii, a(i, 6))
                    a(n, 3) = a(i + ii, 6)
                    ii = ii + 1
                    If i + ii > UBound(a, 1) Then Exit Do
                Loop
            End If
            i = i + ii - 1
        End If
    Next
    With Sheets.Add.Cells(1).Resize(, 4)
        .Value = [{"Employee","Leave Starts","Leave Ends","RequestId"}]
        With .Rows(2).Resize(n)
            .Value = a
            .Sort .Columns(2), , .Columns(1)
            .Columns("b:c").NumberFormatLocal = "ddd, d mmm yyyy"
        End With
        .EntireColumn.AutoFit
    End With
End Sub
Book1
ABCD
1EmployeeLeave StartsLeave EndsRequestId
2Alam Moreira MoralesMon, 6 Jan 2025Tue, 7 Jan 2025Steel Shop - Fabrication
3Amrik Singh ChohanMon, 6 Jan 2025Fri, 10 Jan 2025Steel Shop - Fabrication
4Bi NgachhiWed, 8 Jan 2025Wed, 8 Jan 2025Steel Shop - Fabrication
5Alam Moreira MoralesFri, 10 Jan 2025Fri, 10 Jan 2025Steel Shop - Fabrication
6Alam Moreira MoralesMon, 13 Jan 2025Tue, 14 Jan 2025Steel Shop - Fabrication
7Amrik Singh ChohanMon, 13 Jan 2025Tue, 14 Jan 2025Steel Shop - Fabrication
8Alam Moreira MoralesFri, 17 Jan 2025Fri, 17 Jan 2025Steel Shop - Fabrication
9Alam Moreira MoralesMon, 20 Jan 2025Tue, 21 Jan 2025Steel Shop - Fabrication
10Alam Moreira MoralesFri, 24 Jan 2025Fri, 24 Jan 2025Steel Shop - Fabrication
11Alam Moreira MoralesTue, 28 Jan 2025Tue, 28 Jan 2025Steel Shop - Fabrication
12Billy PitmanMon, 7 Apr 2025Thu, 10 Apr 2025CONCRETE - Tullamarine
Sheet2
 
Upvote 0
Fuji said:
Since you didn't show us your desired result, this is how I read your question.
Code: 
Sub test()
    Dim a, i&, ii&, n&
    a = Sheets("sheet1").[a1].CurrentRegion.Value
    For i = 3 To UBound(a, 1)
        If a(i, 5) <> "" Then
            n = n + 1
            a(n, 1) = a(i, 3): a(n, 2) = a(i, 6)
            a(n, 3) = a(i, 6): a(n, 4) = a(i, 8)
            ii = 1
            If i + ii < UBound(a, 1) Then
                Do While a(i + ii, 6) = DateAdd("d", ii, a(i, 6))
                    a(n, 3) = a(i + ii, 6)
                    ii = ii + 1
                    If i + ii > UBound(a, 1) Then Exit Do
                Loop
            End If
            i = i + ii - 1
        End If
    Next
    With Sheets.Add.Cells(1).Resize(, 4)
        .Value = [{"Employee","Leave Starts","Leave Ends","RequestId"}]
        With .Rows(2).Resize(n)
            .Value = a
            .Sort .Columns(2), , .Columns(1)
            .Columns("b:c").NumberFormatLocal = "ddd, d mmm yyyy"
        End With
        .EntireColumn.AutoFit
    End With
End Sub
Book1
ABCD
1EmployeeLeave StartsLeave EndsRequestId
2Alam Moreira MoralesMon, 6 Jan 2025Tue, 7 Jan 2025Steel Shop - Fabrication
3Amrik Singh ChohanMon, 6 Jan 2025Fri, 10 Jan 2025Steel Shop - Fabrication
4Bi NgachhiWed, 8 Jan 2025Wed, 8 Jan 2025Steel Shop - Fabrication
5Alam Moreira MoralesFri, 10 Jan 2025Fri, 10 Jan 2025Steel Shop - Fabrication
6Alam Moreira MoralesMon, 13 Jan 2025Tue, 14 Jan 2025Steel Shop - Fabrication
7Amrik Singh ChohanMon, 13 Jan 2025Tue, 14 Jan 2025Steel Shop - Fabrication
8Alam Moreira MoralesFri, 17 Jan 2025Fri, 17 Jan 2025Steel Shop - Fabrication
9Alam Moreira MoralesMon, 20 Jan 2025Tue, 21 Jan 2025Steel Shop - Fabrication
10Alam Moreira MoralesFri, 24 Jan 2025Fri, 24 Jan 2025Steel Shop - Fabrication
11Alam Moreira MoralesTue, 28 Jan 2025Tue, 28 Jan 2025Steel Shop - Fabrication
12Billy PitmanMon, 7 Apr 2025Thu, 10 Apr 2025CONCRETE - Tullamarine
Sheet2
Click to expand...
This solution makes more sense as it considers the multiple periods of leave for each person.
 
Upvote 0
Apologies, but based upon your response in post #5, my solution in PQ does not address this. My solution assumes first date and last date in your post are the beginning and end of leave and not multiple start and ends. If you are still interested, I will have to recreate as I did not save the file.
 
Upvote 0
alansidman said:
Apologies, but based upon your response in post #5, my solution in PQ does not address this. My solution assumes first date and last date in your post are the beginning and end of leave and not multiple start and ends. If you are still interested, I will have to recreate as I did not save the file.
Click to expand...
I'm not sure about Prasanna arachchi but I'd be interested in how you identify the different leave periods using Power Query. You can copy the code from your post.
alansidman said:
Apologies, but based upon your response in post #5, my solution in PQ does not address this. My solution assumes first date and last date in your post are the beginning and end of leave and not multiple start and ends. If you are still interested, I will have to recreate as I did not save the file.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
701
Herakles
H
J
  • Question Question
Index Match Formula to generate a report duplicates? Rosters/Vacancies
Replies
9
Views
853
Jay88
J
M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale
N
  • Question Question
Split excel Data into separate Workbook rather then tabs.
Replies
2
Views
633
nihad
N
C
  • Question Question
VBA Code to Generate Report from All Sheets
Replies
0
Views
1K
Cristinky420
C

Forum statistics

Threads
1,225,452
Messages
6,185,059
Members
453,276
Latest member
devilsbarrister

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top