• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to find min and max values in Range

mu4you

New Member
Dear Sirs,

I need to prepare the student attendance sheet from zoom meeting data file,

Kindly need help to find the 1st joined time, last left time and total of minutes.( for understanding purpose used random figures)

I have tried thru Pivot table but getting date 1900 whenever I use min/max and even tried aggregate function but I am not that perfect using aggregate.

Please find attached sample file for your more information, after getting results i need to copy the data in new sheet.
 

Attachments

  • Sample.xlsx
    9.4 KB · Views: 8
Power Query solution at cell I1. Right-click and choose Refresh to update.
 

Attachments

  • Chandoo45636Sample.xlsx
    19.1 KB · Views: 8
Dear Sirs,

I need to prepare the student attendance sheet from zoom meeting data file,

Kindly need help to find the 1st joined time, last left time and total of minutes.( for understanding purpose used random figures)

I have tried thru Pivot table but getting date 1900 whenever I use min/max and even tried aggregate function but I am not that perfect using aggregate.

Please find attached sample file for your more information, after getting results i need to copy the data in new sheet.
Great..... thanks a lot...but I cannot see the formula to apply in my sheet
 
Let's just get you to check that it works for your actual data first:
1. Delete rows 13 to 16 in the file I attached.
2. Paste your real data (not the headers) on top of the data currently there from row 2 downwards.
3. Make sure that the extent of the Excel Table covers all your data (use the grab-handle if necessary but it will probably be correct):
73127
4. Refresh the table at cell I1

If that works OK then we can go about the following:
1. Have both the file I attached and your real file open together.
2. On both, click on the Queries & Connections icon in the Queries & Connections section of the Data tab of the ribbon. You will see this:
73128
3. Right-click Table1 and choose Copy
4. Go to your file, and in the same area right-click and Paste. It will show an error. It may even try to add a new sheet. Ignore.
5. Convert your source data table to a proper Excel Table and makes sure it's called Table1
6. Go back to point 4 above and right-click again and choose Load to… and choose where you want to put your results table:
73129
7. Finally, if necessary, Refresh that new table.

If your real data doesn't resemble your original sample file, attach a realistic file or your real file (via file sharing site perhaps if it's big). If it's sensitive you can password protect it or only give me the link via private conversation here. I can do all the above and ensure it works.
 
Back
Top