+ Reply to Thread
Results 1 to 27 of 27

Attendance Analysis

  1. #1
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Attendance Analysis

    Dear all

    Is there any excel expert out there.
    I really need your expertise.

    I have an excel sheet which contain Student names, class, student attendance and their attendance percentage.

    I will need to further develop the worksheet into a new worksheet where by it will pull out the names and class of those students who had obtain less than 50% from sheet 1 and 2.

    Eg:
    Stephanie (From sheet 1), May and June (From Sheet 2) have attendance less than 50%.
    All 3 of them are from the same class.
    How do I pull of their names and put into a new sheet lets say sheet 3

    Do get back to me regarding my queries.

    Your help is strongly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    Maybe :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    Thanks for your input.

    However i cant picture when you had written.

    Is it possible you put the codes into the excel sheet.

    Thanks

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    To run the macro :
    - Press Alt F8 on keyboard
    - Click "Run" command button
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    This is great. This is the result which i wish to obtain.

    May i know how you did it.

    Your help is strongly appreciated

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    I use the "Collection" object here.
    Collection object acts as container, that holds other data in it.
    You can imagine collection object like a box that holds other items inside it.
    A collection object can put another collection objects in it, like a box that can put another smaller boxes inside it.

    The macro loop for each data on column Q on each sheet, if the data is qualified (< 50 %) then look at column B on the same row.
    If the "bigger box" with that name (like S1-A, S1-C, S1-D, etc) is not exists yet, then create it.
    Then put the "littler boxes" inside right "bigger box", ex. put Stephanie, May, June inside S1-A box, put Snow and Mary inside S2-A box, etc.

    When all data have been distributed, then recollect the data by looping big box -> small box, and store them in array.
    Then the array is written to the new sheet.

  7. #7
    Forum Contributor
    Join Date
    07-07-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    256

    Re: Attendance Analysis

    Hai another way using filter
    if it works,plz tell me.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    Thanks for your input.

    I will go try it out.

  9. #9
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi catchnanan

    Thanks for your input.

    May I know how you did it.

    Is there another way instead of using filter as I had about 39 classes and thousand over students.

    Thanks

  10. #10
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    You are welcome.

    Don't forget to mark this thread as solved :
    please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Regards

  11. #11
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    Noted with thanks

    Once we solve our area I will surely put solved.

    Thanks alot once again

  12. #12
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    I need some help from you.

    What is required to change when I have more worksheet and the rows in the worksheet increase.

    Eg
    Sheet1 to Sheet 20
    Row 17 to Row 54

    Do get back to me regarding my queries as I am unable to solve the issue.

    Thanks

  13. #13
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    You better upload a sample workbook that describe your new case.

  14. #14
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    You may refer to attach.

    Thanks
    Attached Files Attached Files

  15. #15
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    Your data layout is inconsistent, e.g. Last column (also known as % Attendance column) of :
    Sheet1 = Column Y
    Sheet2 = Column Y
    Sheet3 = Column W
    Sheet4 = Column T
    Sheet5 = Column X
    Sheet6 = Column U

    You must change all the sheets layout to a uniformed layout first.

  16. #16
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    I had change all the column to Y.

    You may refer to attach.

    Thanks
    Attached Files Attached Files

  17. #17
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    Should be :
    Please Login or Register  to view this content.
    Last edited by karedog; 11-25-2016 at 02:02 AM. Reason: mod. to accept growing columns

  18. #18
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    May I know how did you derive the code and if i got more sheet what is required to be changed.

    Thanks

  19. #19
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    The macro is to run on all worksheets (it means you can add as many sheets as needed without the need to re-modified the code) that qualified some constraints :

    ---> If Left$(v1.Range("A2"), 6) = "Absent" And Left$(v1.Range("A16"), 6) = "Name" Then
    - First six characters of range A2 on that sheet must be "Absent"
    - Range A16 on that sheet must be "Name"

    Any sheet that meets these criteria will be processed by macro, so you can freely add another sheets (as long as the data layout is same).

  20. #20
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    Noted with thanks.

    I will try and get back to you if there is any other issue i face.

    Thanks for your help.

  21. #21
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi karedog

    I had tried your coding on the excel sheet I send to you, it works perfectly fine.

    However when i tried it on the other excel sheet, the coding doesnt works.

    I got this debug message:

    coll(arr(i, 2))(1).Add .Rows(i)

  22. #22
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    It means that your other excel sheet has different layout, the code need to modified based on your new layout.
    Please upload your file.

  23. #23
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    You may refer to the attaches.

    Your help is strongly appreciated.

    Thanks
    Attached Files Attached Files

  24. #24
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Attendance Analysis

    As expected, your data layout is different from first post, and your new data layout have so many inconsistencies :
    - You add a new column (S/N --> column A), this makes all data is shifted to right, and of course, calculation will be wrong
    - Data sometime start from row 2 (Sheet1 - Sheet5), sometime from row 1 (Sheet6 - Sheet20)
    - % Attendance column location is always changed, for the last workbook it is located at column Y, for Sample1.xlsx it is at column J, for Sample2.xlsx is at column L
    - Sample2.xlsx, this workbook has NO DATA entered yet !!! How can you expect macro run on empty tables ??!!

    So :
    - STOP keep changing your data layout, if you keep doing this, don't expect the macro will be worked, and don't expect me to remake the macro anytime you rechange the layout
    - Don't do something silly like "the macro is not worked" on workbook WITH EMPTY TABLES like Samples2.xlsx, I don't understand what your motive by doing this

    This macro will work on Sample1.xlsx, and Sample2.xlsx if Sample2.xlsx has already filled with some data, don't expect this can run if you rechange the data layout again.
    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi Karedog

    Thanks alot for your help and sorry for the inconsistency in the sheets.

    My apologise for the confusion.

    You had solve my problem and i appreciate your help.

    As for sample 2 is also my mistake. I uploaded the wrong file with no details in it. So sample 2 can be ignored.

    Thanks once again for all the help you had done and sorry for the inconvenient once again.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,342

    Re: Attendance Analysis

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  27. #27
    Registered User
    Join Date
    11-17-2016
    Location
    Singaoire
    MS-Off Ver
    2013
    Posts
    27

    Re: Attendance Analysis

    Hi karedog

    Thanks for your help in solving my excel issue with micro and sorry for the trouble that I had given to you.

    I not sure is my further development of the code you had given to me is the same as the one i seek help for.

    Now that i am able to pull out the names for those students with low attendance rate.

    Next I wish to develop is by using excel to pull out students from the same class and their attendance.

    You may refer to the attach file.

    Your help is strongly appreciated.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 03-17-2016, 05:34 AM
  2. Need Help - AutoCalculate Attendance Points for employee attendance records
    By switzd0d in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 03:00 PM
  3. Replies: 1
    Last Post: 03-06-2014, 11:00 AM
  4. Replies: 2
    Last Post: 06-28-2013, 08:43 AM
  5. Data Analysis- advanced pivot functions for employee analysis
    By Dsankie in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-10-2013, 12:30 AM
  6. Replies: 3
    Last Post: 06-12-2011, 02:56 PM
  7. [SOLVED] Analysis ToolPak installed but no Data Analysis option
    By Eric Stephens in forum Excel General
    Replies: 3
    Last Post: 02-02-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1